Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Bernie,
I'm still getting the same error message and the debugger is highlighting the line: With Worksheets("SHEET" & i) Gary Beard |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Bernie,
NO...I forgot to mention that. I've renamed each on to represent the different counties I am working with. -- Gary Beard |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 part question - macro / command button | Excel Discussion (Misc queries) | |||
delete a macro button is grayed out. | Excel Worksheet Functions | |||
Assigning a macro to a "button" | Excel Discussion (Misc queries) | |||
How to assign a macro to a commnd button | Excel Discussion (Misc queries) | |||
assign a macro to a control button | Excel Discussion (Misc queries) |