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

Loop through array of worksheets
 

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

Loop through array of worksheets
 

Ahhh - Thanks Bob

"Bob Phillips" wrote in message
...
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

Loop through array of worksheets
 

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" wrote in message
...
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

Loop through array of worksheets
 

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" wrote in message
...
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" wrote in message
...
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