Modifying Sheet1 macro to run on Sheet2

[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

 

[Dave Peterson]

Do you have Merged cells on Sheet2? Merged cells are a pain when sorting.

 

[Guest]

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.

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Guest]

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

 

[Dave Peterson]

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