Selecting multiple sheets in Excel 1

[DeGeus]

I have a excel workbook which can have a different number of sheets. With a VBA program I check the the content of the sheets. For this I have to loop trough the sheets. Now I want to select multiple sheets but this can differ from run to run. als the sheet names can differ from run to run.

I know how to store the names in a matrix and I wanted to use this matrix to select multiple sheets bus this doesn't work. Can you please give me a solution. Hereunder I give a example of how I wanted to solve the problem.

Dim bladen(6) As String
Sub Macro1()
bladen(1) = "Sheet1"
bladen(2) = "Sheet2"
bladen(3) = "Sheet3"
bladen(4) = "Sheet4"
bladen(5) = "Sheet5"
bladen(6) = "Sheet6"
Sheets(Array(bladen)).Select
Sheets("Blad6").Activate
End Sub
In the program I find the names of the sheets with a for next loop so thats no problem, the problem is the selection of a unknown number of sheets.
I'm not an expert so its quit simple, thats no problem as long as it works.

thanks for your time

Leendert de Geus

http://WWW.CEC-CES.nl

 

[xlbo]

This should give you an idea:

Dim Bladen() as variant
Bladen = array("Sheet1","Sheet2","Sheet3")
Sheets(Bladen()).select

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DeGeus]

This helps me partly. Because I don't know the contents of the array. You have given an example of three sheets. But this can be one to lets say ten sheets. Also the names of the sheets is not fixed. So is there a way to make that flexible then it's the perfect solution.

For example. I have a workbook with lets say 10 sheets. 5 sheets starts wit a name A with a sequense number and 5 sheets with a name B with a sequence number. I only want the B sheets. So I make a loop
for t= 1 to sheets.count
if left(sheets(t).name;1)="B" then
add to array but how???
end if
next

Leendert de Geus

http://WWW.CEC-CES.nl

 

[xlbo]

Tested thisa and it works. For more info look up
ReDim
Preserve
Array
Option Base

Option Base 1
Sub Select_Multiple_Sheets()
Dim Bladen() As Variant, Ctr As Integer

ReDim Bladen(Sheets.Count)
Ctr = 1

For t = 1 To Sheets.Count
  If Left(Sheets(t).Name, 1) = "B" Then
    Bladen(Ctr) = Sheets(t).Name
    Ctr = Ctr + 1
  End If
Next

ReDim Preserve Bladen(Ctr - 1)

Sheets(Bladen()).Select

End Sub

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DeGeus]

The program works until this line:

Sheets(Bladen()).Select

Then I get an Error 9 message.

For your info I'm working with Micrososft Excel 2000 SP-3

And again many thanks foor your support so far

Leendert

Leendert de Geus

http://WWW.CEC-CES.nl

 

[xlbo]

Did you copy the code exactly as is ?? including the Option Base 1 ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DeGeus]

Yes I did.

But there was another procedure in the same module for wich I declared some things outside the procedure. Now I removed this procedure an dit works Now it works. You get a big, big star from me for your patience and good advices.

Many many thanks

Leendert de Geus

http://WWW.CEC-CES.nl

 

[xlbo]

Excellent - was getting slightly worried then as it worked fine on my test workbook !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question