Loop through array of worksheets

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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

Ahhh - Thanks Bob

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
> 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,

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

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

"Andibevan" <[email protected]> wrote in message
news:%[email protected]...
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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>