How can I modify this code to reference cells in a different worksheet

Question

Sub SortDataWithoutHeader()
Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub

my interface buttons are in a sheet called 'tools' and the data being manipulated is in a sheet called 'data'

Anywhere you have a Range call, add ThisWorkbook.Worksheets("Data"). beforehand. — BigBen
– BigBen, Commented Jul 9, 2020 at 13:31
It gives me "Runtime error 1004 - Application-defined or Object-defined error. Screenshot imgur.com/kYKKfKV — EyyJP
– EyyJP, Commented Jul 9, 2020 at 13:45

BigBen · Accepted Answer · 2020-07-09 13:48:27Z

1

Something like this. A With statement can be helpful here. Note the periods before every Range, Cells, and Rows call within the With block. The period is necessary to actually reference ws, which is the sheet you want.

Sub SortDataWithoutHeader()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    With ws
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        .Range("A1:A" & lastRow).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo
    End With
End Sub

answered Jul 9, 2020 at 13:48

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I modify this code to reference cells in a different worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related