Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to select sheets
Hi,
I need to select an array of sheets, starting from the first sheet to a sheet before the sheet "Acc". What is the code for this procedure? I can't put in the names of the sheets coz they change frequently except for the sheet "Acc". Thanks. noel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to select sheets
Noel
The code below should work Sub SelectToAcc() Dim x As Long Dim sShtName As String Dim vGrpSht() As String x = 1 ReDim vGrpSht(ThisWorkbook.Worksheets.Count) Do While sShtName < "Acc" If x ThisWorkbook.Worksheets.Count Then Exit Sub sShtName = ThisWorkbook.Worksheets(x).Name vGrpSht(x) = sShtName x = x + 1 Loop ReDim Preserve vGrpSht(x - 1) ThisWorkbook.Worksheets(vGrpSht()).Select End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "noel" wrote in message ... Hi, I need to select an array of sheets, starting from the first sheet to a sheet before the sheet "Acc". What is the code for this procedure? I can't put in the names of the sheets coz they change frequently except for the sheet "Acc". Thanks. noel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to select sheets
Hi,
but the macro cannot run properly. It stops at the line "ThisWorkbook.Worksheets(vGrpSht()).Select". what i wanted to do was to change the fonts for "a2:c2" to red and print and change it back to black again. So I added the following to your code: ActiveSheets.Range("a2:c2").Select Selection.Font.ColorIndex = 3 ThisWorkbook.PrintOut Range("a2:c2").Select Selection.Font.ColorIndex = 2 Range("M35").Select It doesn't work. But even if I do not add the above, the code you gave me also stops at that last line. Why is this so? Sorry, I'm very new to VBA macros. Regards, Val -----Original Message----- Noel The code below should work Sub SelectToAcc() Dim x As Long Dim sShtName As String Dim vGrpSht() As String x = 1 ReDim vGrpSht(ThisWorkbook.Worksheets.Count) Do While sShtName < "Acc" If x ThisWorkbook.Worksheets.Count Then Exit Sub sShtName = ThisWorkbook.Worksheets(x).Name vGrpSht(x) = sShtName x = x + 1 Loop ReDim Preserve vGrpSht(x - 1) ThisWorkbook.Worksheets(vGrpSht()).Select End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England "noel" wrote in message ... Hi, I need to select an array of sheets, starting from the first sheet to a sheet before the sheet "Acc". What is the code for this procedure? I can't put in the names of the sheets coz they change frequently except for the sheet "Acc". Thanks. noel . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to select sheets
Noel
Just came to me...At the top of the code, outside of the procedure (macro) itself type 'Option Base 1' (No quotes) Sorry my fault. I hate using zero based arrays I don't think the route you are taking will work however as the print action seems to deselect the sheets, at least in my small test, you are probably better to cycle through the sheets Can someone pick this up for me as cab is outside with meter running! Sorry -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Nick Hodge" wrote in message ... Noel I am off on holiday tomorrow, but it all worked ok for me. Can you post back with the error you get and someone will diagnose that issue I'm sure? It may also help to know your 'end game' so that someone may give you a better route -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Noel" wrote in message ... Hi, but the macro cannot run properly. It stops at the line "ThisWorkbook.Worksheets(vGrpSht()).Select". what i wanted to do was to change the fonts for "a2:c2" to red and print and change it back to black again. So I added the following to your code: ActiveSheets.Range("a2:c2").Select Selection.Font.ColorIndex = 3 ThisWorkbook.PrintOut Range("a2:c2").Select Selection.Font.ColorIndex = 2 Range("M35").Select It doesn't work. But even if I do not add the above, the code you gave me also stops at that last line. Why is this so? Sorry, I'm very new to VBA macros. Regards, Val -----Original Message----- Noel The code below should work Sub SelectToAcc() Dim x As Long Dim sShtName As String Dim vGrpSht() As String x = 1 ReDim vGrpSht(ThisWorkbook.Worksheets.Count) Do While sShtName < "Acc" If x ThisWorkbook.Worksheets.Count Then Exit Sub sShtName = ThisWorkbook.Worksheets(x).Name vGrpSht(x) = sShtName x = x + 1 Loop ReDim Preserve vGrpSht(x - 1) ThisWorkbook.Worksheets(vGrpSht()).Select End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England "noel" wrote in message .. . Hi, I need to select an array of sheets, starting from the first sheet to a sheet before the sheet "Acc". What is the code for this procedure? I can't put in the names of the sheets coz they change frequently except for the sheet "Acc". Thanks. noel . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select all sheets | Excel Discussion (Misc queries) | |||
macro to select all sheets | Excel Discussion (Misc queries) | |||
Macro to select and print sheets | Excel Discussion (Misc queries) | |||
Select Sheets via Array Macro | Excel Programming | |||
macro to select sheets/page in a workbook and print them | Excel Programming |