Hello,

I am writing a VBA macro that copies data from 19
worksheets on a master "roll up" sheet. Once they are all
copied, I want to sort the columns but I am having trouble
getting the sheet to sort the selection I am telling it
to. I tried the below code, but it bombs out:

Range("C2").Select
UpperLeft2 = ActiveCell.Address
Range("N2").Select
Selection.End(xlDown).Select
BottomRight2 = ActiveCell.Address
Range(UpperLeft2, BottomRight2).Select
Range(UpperLeft2, BottomRight2).sort Key1:=Range("B3"),
Order1:=xlAscending, Key2:=Range _
("E3"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortTextAsNumbers

Using "Selection.CurrentRegion.Select" gives me the same
area I desire, but I can't get that to work the
the ".sort" command either. The only way it will sort is
if I select the top left cell (being C2 in my example) and
then record a macro sorting how I want...but doing it this
way creates code that says, "Range("C2:C622").sort..."
which will not work for my workbook because there can, and
will be, rows of data beyond 622 in the future.

I need a more flexible solution and I have not been able
to get it to work - help!

Thanks!

- Ryan

Your Key1 is column B, but that isn't the sort range - this raises an error.

--
Regards,
Tom Ogilvy


"Ryan" wrote in message
...
Hello,

I am writing a VBA macro that copies data from 19
worksheets on a master "roll up" sheet. Once they are all
copied, I want to sort the columns but I am having trouble
getting the sheet to sort the selection I am telling it
to. I tried the below code, but it bombs out:

Range("C2").Select
UpperLeft2 = ActiveCell.Address
Range("N2").Select
Selection.End(xlDown).Select
BottomRight2 = ActiveCell.Address
Range(UpperLeft2, BottomRight2).Select
Range(UpperLeft2, BottomRight2).sort Key1:=Range("B3"),
Order1:=xlAscending, Key2:=Range _
("E3"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortTextAsNumbers

Using "Selection.CurrentRegion.Select" gives me the same
area I desire, but I can't get that to work the
the ".sort" command either. The only way it will sort is
if I select the top left cell (being C2 in my example) and
then record a macro sorting how I want...but doing it this
way creates code that says, "Range("C2:C622").sort..."
which will not work for my workbook because there can, and
will be, rows of data beyond 622 in the future.

I need a more flexible solution and I have not been able
to get it to work - help!

Thanks!

- Ryan

Whoop! I fixed that, but it still doesn't work...it
doesn't like the code I am using to specify the range to
sort...I'm stumped!

Nevermind Tom, I got it to work...thanks for your help!

Have you tried:

Selection.CurrentRegion.Sort Key1:=Range("B3"),
Order1:=xlAscending, Key2:=Range _
("E3"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortTextAsNumbers

Using "Selection.CurrentRegion.Select" gives me the same
area I desire, but I can't get that to work the
the ".sort" command either.

You don't need to select, just change .Select for .Sort

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.