Hi,
Rather that looking for the sheets to copy, why not look for the sheets not
to copy?

dim intNum, intCount as integer
dim strSheetNamer1, shtName2 as string
strSheetName1="Not This One"
strSheetName2="Or This One"

intCount=sheets.count

for intNum=1 to intCount-2
'minus 2 because of those 2 you do not want
if sheets(intNum) .name<strSheetName1 or _
sheets(intNum) .name<strSheetName2 _
then
sheets(intNum).Copy
'to whereever

end if
next intnum






"munt via OfficeKB.com" wrote:

Hi,

I have a workbook with about 6 worksheets on it. I only want to copy 4 of
those worksheets to a new workbook but, not all of the worksheets exist.

The following code works.
Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Select
Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Copy


If I build a string with only the sheets I want to copy I get a "Subscript
out of range" error.

worksheet_exists = ""
Set worksheet_exists = Sheets("Accounts")
If worksheet_exists Is Nothing Then
Else
If worksheet_to_copy = "" Then
worksheet_to_copy = "Accounts"
End If
End If

Set worksheet_exists = Sheets("Expenses")
If worksheet_exists Is Nothing Then
Else
If worksheet_to_copy = "" Then
worksheet_to_copy = "Expenses"
Else
worksheet_to_copy = worksheet_to_copy & """, ""Expenses"
End If
End If

Set worksheet_exists = Sheets("Tax")
If worksheet_exists Is Nothing Then
Else
If worksheet_to_copy = "" Then
worksheet_to_copy = "Tax"
Else
worksheet_to_copy = worksheet_to_copy & """, ""Tax"
End If
End If

Set worksheet_exists = Sheets("September Tax")
If worksheet_exists Is Nothing Then
Else
If worksheet_to_copy = "" Then
worksheet_to_copy = "September Tax"
Else
worksheet_to_copy = worksheet_to_copy & """, ""September Tax"
End If
End If

' This gives me an error - Subscript out of range

Sheets(Array(worksheet_to_copy)).Select
Sheets(Array(worksheet_to_copy)).Copy