I have placed a macro button on my sheet and I would like it to sort A2:P150
of SHEET2 through SHEET7. I would like it to sort the data on those sheets
by the column that is called in cell C3 of SHEET1.
Is this possible?
BTW, I'm a recreational user of VB, not a programmer. So, simply answering
yes probably won't help me much :-)


--
Gary (I know there's an Excel Programming group that this might be better
posted in, but I enjoy working with the people in this group and thought I'd
post it here first) Beard

Gary,

Yes. Assuming: your headers are in row 2, the value in cell C3 of SHEET1 is
numeric (3 for column C, 5 for E, etc.), and you want to sort ascending, try
this:

Sub GarySheetSorter()
Dim i As Integer
For i = 2 To 7
Worksheets("SHEET" & i).Range("A2:P150").Sort _
Key1:=Range("A2").Item(1, Worksheets("SHEET1").Range("C3").Value), _
Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next i
End Sub

P.S. The "people in this group" are the same as the people in the
programming group: we just tend to be nicer over here ;-)

HTH,
Bernie
MS Excel MVP


"gbeard" wrote in message
.. .
I have placed a macro button on my sheet and I would like it to sort
A2:P150
of SHEET2 through SHEET7. I would like it to sort the data on those
sheets
by the column that is called in cell C3 of SHEET1.
Is this possible?
BTW, I'm a recreational user of VB, not a programmer. So, simply
answering
yes probably won't help me much :-)


--
Gary (I know there's an Excel Programming group that this might be better
posted in, but I enjoy working with the people in this group and thought
I'd
post it here first) Beard

Bernie,
I get the following error when I run this:

"runtime error 9"
"subscript out of range"

So, I changed the following, but get the same results.
My headers are in row 1, so I changed the macro to A1:P150. I also changed
the Key1:=Range("A2") to Key1:=Range("A1").
I also had 9 sheets so I changed the For i=2 to 7 to For i=2 to 8.

Any ideas what might be amiss?

Thanks for your help,
Gary Beard

Gary,

Any ideas what might be amiss?

Yeah, I'm lazy, and stupid, and forgot about needing to fully qualify the
key range. Use this, fully tested:

Sub GarySheetSorter2()
Dim i As Integer
For i = 2 To 8
With Worksheets("SHEET" & i)
.Range("A1:P150").Sort _
Key1:=.Range("A1").Item(1, Worksheets("SHEET1").Range("C3").Value), _
Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Next i
End Sub


HTH,
Bernie
MS Excel MVP


"gbeard" wrote in message
. ..
Bernie,
I get the following error when I run this:

"runtime error 9"
"subscript out of range"

So, I changed the following, but get the same results.
My headers are in row 1, so I changed the macro to A1:P150. I also
changed
the Key1:=Range("A2") to Key1:=Range("A1").
I also had 9 sheets so I changed the For i=2 to 7 to For i=2 to 8.

Any ideas what might be amiss?

Thanks for your help,
Gary Beard

Bernie,
I'm still getting the same error message and the debugger is highlighting
the line:

With Worksheets("SHEET" & i)


Gary Beard

Gary,

Are your sheets named exactly (no extra spaces)

SHEET2, SHEET3, SHEET4, SHEET5, SHEET6, SHEET7, SHEET8?

HTH,
Bernie
MS Excel MVP


"gbeard" wrote in message
. ..
Bernie,
I'm still getting the same error message and the debugger is highlighting
the line:

With Worksheets("SHEET" & i)


Gary Beard