Loop through array of worksheets

[Andibevan]

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

 

[Bob Phillips]

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

 

[Andibevan]

Ahhh - Thanks Bob

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

 

[Andibevan]

Bob,

Some of my cells are over 255 characters - how do I overcome this as it
throws a run-time error due to the size of various cells.

Thanks

Andi

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

 

[Andibevan]

Also - as the array is 0 based - should the upper limit for i be
Ubount(Sheet_Data)-1

Interestingly - when I changed this, the prious problem to do with 255
characters seems to have gone away?

Ta

Andi

Bob,

Some of my cells are over 255 characters - how do I overcome this as it
throws a run-time error due to the size of various cells.

Thanks

Andi

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