Hello Norman,
Yes, I think you are right. I only tested it when the two unwanted sheets
were at the end of the collection. Thanks for making that point. I think it
would be best to put in a blank Else statement so that the macro has an
option if the statement is false.

- -mark


"Norman Jones" wrote:

Hi Exceluserforeman,

for intNum=1 to intCount-2
'minus 2 because of those 2 you do not want

I think that you would need to iterate though the entire sheets collection,
unless you know that the two sheets in question are the last two sheets. Of
course, if this were the case, there would be no need to effect the name
comparisons:

if sheets(intNum) .name<strSheetName1 or _
sheets(intNum) .name<strSheetName2 _

---
Regards,
Norman



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