Macro Button
 

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

Bernie,
NO...I forgot to mention that. I've renamed each on to represent the
different counties I am working with.

--
Gary Beard

Well, that's a big change from what you said.

Try this, after changing the "Sheet1" to be the actual name of the sheet
with cell C3 having the column number- and make sure you put it in quotes.
This will sort all the other sheets.

HTH,
Bernie
MS Excel MVP

Sub GarySheetSorter3()
Dim mySht As Worksheet
Dim Sheet1Name As String
Sheet1Name = "Sheet1"

For Each mySht In ThisWorkbook.Worksheets
If mySht.Name < Sheet1Name Then
With mySht
.Range("A1:P150").Sort _
Key1:=.Range("A1").Item(1, Worksheets(Sheet1Name).Range("C3").Value), _
Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End If
Next i
End Sub



"gbeard" wrote in message
.. .
Bernie,
NO...I forgot to mention that. I've renamed each on to represent the
different counties I am working with.

--
Gary Beard

Bernie,
We're getting close here. I'm getting this error:

Compile error:

Invalid Next control variable reference.

When I go into the debugger the line "Next i" is highlighted.

Where to now boss? Hopefully I'll get it figured out before you get up this
morning...but I'm not counting on it.

--
Gary Beard

Gary,

Sorry, I forgot to change:

Next i

to

Next mySht

Then it should all work.

HTH,
Bernie
MS Excel MVP


"gbeard" wrote in message
...
Bernie,
We're getting close here. I'm getting this error:

Compile error:

Invalid Next control variable reference.

When I go into the debugger the line "Next i" is highlighted.

Where to now boss? Hopefully I'll get it figured out before you get up
this
morning...but I'm not counting on it.

--
Gary Beard

Bernie,
That returns the "Runtime error 9 Subscript out of range" error. The
debugger is highlighting:

..Range("A1:P150").Sort _
Key1:=.Range("A1").Item(1, Worksheets(Sheet1Name).Range("C3").Value), _
Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

--
Gary Beard

Bernie,
That worked! I just forgot to change the Sheet1 name again.
I think for now that is the last project I have for this workbook. I
appreciate all the help you've given me on this project. You've inspired me
to get an Excel book from the library (it's on hold for me to pick it up
today) so hopefully I'll be able to figure a lot more out on my own now.

Thanks again Bernie,
Gary Beard

Gary,

If you send me your file, I will get it to work. It's just easier to do
that sometimes.

Remove any sensitive data, and send it to me: take the spaces out, and
change the dot to .

HTH,
Bernie
MS Excel MVP


"gbeard" wrote in message
...
Bernie,
That returns the "Runtime error 9 Subscript out of range" error. The
debugger is highlighting:

.Range("A1:P150").Sort _
Key1:=.Range("A1").Item(1, Worksheets(Sheet1Name).Range("C3").Value),
_
Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

--
Gary Beard