Macro Button

G

gbeard

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 :)
 
B

Bernie Deitrick

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
 
G

gbeard

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
 
B

Bernie Deitrick

Gary,
Any ideas what might be amiss?
Click to expand...

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
 
G

gbeard

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

With Worksheets("SHEET" & i)


Gary Beard
 
B

Bernie Deitrick

Gary,

Are your sheets named exactly (no extra spaces)

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

HTH,
Bernie
MS Excel MVP
 
G

gbeard

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

Bernie Deitrick

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
 
G

gbeard

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.
 
B

Bernie Deitrick

Gary,

Sorry, I forgot to change:

Next i

to

Next mySht

Then it should all work.

HTH,
Bernie
MS Excel MVP
 
G

gbeard

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
 
G

gbeard

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
 
B

Bernie Deitrick

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
 
Top