PDA

View Full Version : Referring to an array in many subs



tammyl
04-06-2010, 01:03 AM
Hi,

I have a sheet array that i wish to refer to in multiple sub routines.

this is what i have so far (in one module)

Dim x as Object
Dim Shts as Variant

Sub One_Colour
....
Shts = Sheets(Array("AnnualA4", "AnnualA5"))
ColourSheet
....
End Sub

Sub ColourSheet
For each x in Sheets(Shts)
x.Activate
.....
End sub

I get a run-time error 450 wrong number of arguments, etc
i have tried to change the dim Shts variable to object, string, etc but still get errors.

Hope someone can assist.

Thanks tammyl

Bob Phillips
04-06-2010, 01:07 AM
Dim x As Object
Dim Shts As Variant

Sub One_Colour()
....
Shts = Sheets(Array("AnnualA4", "AnnualA5"))
Call ColourSheet(Shts)
....
End Sub

Sub ColourSheet()
For Each x In Sheets(Shts)
x.Activate
.....
End Sub