Sorting Columns using VBA

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

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!
 
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.
Click to expand...

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

Regards,
 
Back
Top