macro to select sheets

[noel]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Noel]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Nick Hodge]

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
(e-mail address removed)