You cannot use For Each on an array, you have to index through it

Sub Seperate_SMR()
Dim sh As Worksheet
Dim i As Long
'Dim sh As Variant
'sFileName = "BackupDB_" & Format(Date, "yyyymmdd") & "_" &
Format(Time,"hhmmss")
Dim Sheet_Data(2) As Variant
Sheet_Data(0) = "Project Log Form"
Sheet_Data(1) = "Risk Management Plan"

For i = LBound(Sheet_Data) To UBound(Sheet_Data)
Set sh = Worksheets(Sheet_Data(i))
sh.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Temp\Test\" & sh.Name & _
Format(Date, "yyyymmdd") & ".xls"

Application.DisplayAlerts = True
ActiveWorkbook.Close Savechanges:=False
Next

End Sub



--
HTH

Bob Phillips

"Andibevan" wrote in message
...
Hi All,

I have written the following code to save specified named worksheets that
are held in an array.

What I can't get right is the looping through the array - I get the error
"Complie error: For Each control variable on arrays must be variant"

How would I set some worksheet names to an array and then loop through
them?

Thanks in advance

Andi

Sub Seperate_SMR()
Dim sh As Worksheet
'Dim sh As Variant
'sFileName = "BackupDB_" & Format(Date, "yyyymmdd") & "_" & Format(Time,
"hhmmss")
Dim Sheet_Data(2) As Variant
Sheet_Data(0) = "Project Log Form"
Sheet_Data(1) = "Risk Management Plan"

For Each sh In Sheet_Data

sh.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Temp\Test\" & sh.Name & _
Format(Date, "yyyymmdd") & ".xls"

Application.DisplayAlerts = True
ActiveWorkbook.Close Savechanges:=False
Next

End Sub