Modifying Sheet1 macro to run on Sheet2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

The following macro sorts dates and corresponding information (columns A-Q
and S-AI) in ascending order:

I can successfullly run it on Sheet1. Sheet1 does not have any headers and
the data begins on Row 1.

I was thinking I might want to run the also on Sheet2 as well. So I made a
second copy and changed the Column and Row references, but it doesn't seem to
work. Furthermore, I added the syntax "ActiveSheet" at the beginning
thinking that this would make it run on any sheet I had selected.

Here's the code:

'Successfully runs on Sheet1:
Public Sub Sort()
ActiveSheet.Columns("A:Q").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Key3:=Range("C1"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll ToRight:=18
ActiveSheet.Columns("S:AI").Select
Selection.Sort Key1:=Range("S1"), Order1:=xlAscending, Key2:=Range("T1") _
, Order2:=xlAscending, Key3:=Range("U1"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("a1").Select
End Sub

'Returns a Run-time error saying the cells must be identically sized
(referring to rows 1-4 (I think) when I try to run this on Sheet2

Public Sub Sort2()
ActiveSheet.Columns("A:Q").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll ToRight:=18
ActiveSheet.Columns("S:AI").Select
Selection.Sort Key1:=Range("S5"), Order1:=xlAscending, Key2:=Range("T5") _
, Order2:=xlAscending, Key3:=Range("U5"), Order3:=xlAscending,
Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("a1").Select
End Sub

Any ideas what I'm doing wrong for trying to run this on Sheet2? I have
these saved in Module1 in the Module folder.

Thanks so much,

Sharon
 
Yes. I thought though that if I just changed the code to say A5 instead of
A1 (and so on) and changed the Header:= xlNo to Header:= xlYes that it would
start on row 5 but it doesn't. It seems to want to start on Row 1.
 
Sorry, in my previous response I accidentally clicked that the question was
answered. I'm still wondering where my logic is wrong and how to fix it.

Thanks for any help,

Sharon
 
Since you're selecting the range, you'll want to start the selection at A5:

ActiveSheet.Columns("A:Q").Select
Becomes
ActiveSheet.Range("A5:Q65536").Select

If row 4 has the merged cells, then headers = xlno.
 
Okay, I think I'm getting closer, but still no cigar - but for a different
reason. Here's what I did:

Public Sub Sort2()
ActiveSheet.Range("A5:Q400").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll ToRight:=18
ActiveSheet.Range("S5:AI400").Select
Selection.Sort Key1:=Range("S5"), Order1:=xlAscending, Key2:=Range("T5") _
, Order2:=xlAscending, Key3:=Range("U5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("a5").Select
End Sub
------------------------------------------------------

So now when I run this macro it works on Sheet2 but it returns the data from
Columns A:Q on row 375 instead of just rearranging the data and beginning on
Row 1 and it returns the data from Columns S:AI on Row 340. ???

Not sure if this makes a difference but the original data is copied and
pasted onto Sheet1. Beginning on Row 5 of Sheet2 in Columns A: Q and S:AI
are the formulas, =Sheet1!A1, etc. So when I run the macro on Sheet2 it is
technically "looking" at Sheet1 through the formula. Would that make a
difference?

Any ideas why this works but consistently returns the values on Rows 375 and
340?

Thanks,

Sharon
 
Sorting cells that have formulas like that =sheet1!a1 won't work.

I like to find a unique key value and make that a constant. Then I fill in the
other cells with =vlookup() formulas.


Okay, I think I'm getting closer, but still no cigar - but for a different
reason. Here's what I did:

Public Sub Sort2()
ActiveSheet.Range("A5:Q400").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll ToRight:=18
ActiveSheet.Range("S5:AI400").Select
Selection.Sort Key1:=Range("S5"), Order1:=xlAscending, Key2:=Range("T5") _
, Order2:=xlAscending, Key3:=Range("U5"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("a5").Select
End Sub
------------------------------------------------------

So now when I run this macro it works on Sheet2 but it returns the data from
Columns A:Q on row 375 instead of just rearranging the data and beginning on
Row 1 and it returns the data from Columns S:AI on Row 340. ???

Not sure if this makes a difference but the original data is copied and
pasted onto Sheet1. Beginning on Row 5 of Sheet2 in Columns A: Q and S:AI
are the formulas, =Sheet1!A1, etc. So when I run the macro on Sheet2 it is
technically "looking" at Sheet1 through the formula. Would that make a
difference?

Any ideas why this works but consistently returns the values on Rows 375 and
340?

Thanks,

Sharon
Click to expand...
 
Back
Top