Sorting data in sheet from workbook module code

[Thebatfink]

Hi,

I'm scratching my head :/ I'm simply trying to perform a sort on some cells, but the code is in a workbook module. I have found a solution, but I don't understand why I need to do it..

Workbooks(twfn).Worksheets("compiled").Range(Cells(2, pastecol), Cells(pastelastrow + 1, _
pastecol + 1)).Sort Key1:=Workbooks(twfn).Worksheets("compiled").Cells(2, pastecol)

Thats the code I'm using, but why do I need to activate sheet "compiled" with

Workbooks(twfn).Worksheets("compiled").Activate

before it will work?? I've specified the Workbook name AND the Worksheet name?

Thanks!
Batfink

 

[Hermanito]

It is because of the two Cells references in red, they are not fully qualified

Workbooks(twfn).Worksheets("compiled").Range([COLOR=red][B]Cells[/B][/COLOR](2, pastecol), [COLOR=red][B]Cells[/B][/COLOR](pastelastrow + 1, _
pastecol + 1)).Sort Key1:=Workbooks(twfn).Worksheets("compiled").Cells(2, pastecol)

Change them both into
Workbooks(twfn).Worksheets("compiled").Cells
and it will work without activating the sheet.

In order to keep the code shorter and more readable, you'd best use With/End With, like this:

With Workbooks(twfn).Worksheets("compiled")
   .Range(.[COLOR=black]Cells[/COLOR](2, pastecol), .[COLOR=black]Cells[/COLOR](pastelastrow + 1, pastecol + 1)).Sort _
       Key1:=.Cells(2, pastecol)
End With

 

[Thebatfink]

Hi,

Thanks for the reply. I understand now, didn't realise that with the cells.

Funnily enough I did try and use With, but it doesn't sort the data. It doesn't error, but it doesn't perform the sort either?

I've just substituted my sort line with your With / End With statement and nothing gets sorted? Why would that be?

Thanks!

 

[Hermanito]

What are the values for pastecol and pastelastrow when you get to the sort code?