Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Modifying Sheet1 macro to run on Sheet2
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sharon
 
Posts: n/a
Default Modifying Sheet1 macro to run on Sheet2
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
Reply With QuoteReply With Quote
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default
Do you have Merged cells on Sheet2? Merged cells are a pain when sorting.

Sharon wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Sharon
 
Posts: n/a
Default
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.

"Dave Peterson" wrote:

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

Sharon wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Sharon
 
Posts: n/a
Default
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" wrote:

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

Sharon wrote:

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

Reply With QuoteReply With Quote
  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default
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.

Sharon wrote:

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" wrote:

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

Sharon wrote:

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


--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Sharon
 
Posts: n/a
Default
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" wrote:

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.

Sharon wrote:

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" wrote:

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

Sharon wrote:

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


--

Dave Peterson

Reply With QuoteReply With Quote
  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default
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.



Sharon wrote:

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" wrote:

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.

Sharon wrote:

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" wrote:

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

Sharon wrote:

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


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking for comparable data records between Sheet1 and Sheet2 Jim May Excel Discussion (Misc queries) 3 April 1st 05 08:04 PM
can i type sheet1 A5 and make it show Sheet2 A6 cell HelpMe Excel Discussion (Misc queries) 1 February 25th 05 01:44 AM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 12:40 AM
Copy values from Sheet1 to Sheet2 Eintsein_mc2 Excel Discussion (Misc queries) 1 January 6th 05 05:02 AM
Moving from 1 row sheet1 to sheet2 tomc112 Excel Worksheet Functions 5 November 24th 04 09:46 PM


All times are GMT +1. The time now is 05:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement