Copy only some existing worksheets to a new workbook

[munt via OfficeKB.com]

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

 

[exceluserforeman]

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

 

[Norman Jones]

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:

 

[munt via OfficeKB.com]

Hi,

Thanks for that - it almost worked.

It's now copying each spreadsheet to a seperate workbook whereas I wanted
them in one workbook.

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

Hi,

[quoted text clipped - 51 lines]

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

 

[exceluserforeman]

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

 

[munt via OfficeKB.com]

Hi Mark,

Thanks for all your help.

When I copied the code into my Macro I found the problem Norman identified.

Anyway as I said before it worked but copied each sheet to a seperate
workbook. I have now changed tack and copied ALL the sheets to a new
workbook I then use your example to read through the sheets and delete the
ones I don't want.

Thanks heaps for pointing me in a different direction, I was so focused on
copying only the sheets I wanted and trying to resolve my error that I didn't
look at other options.

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

Hi Exceluserforeman,

[quoted text clipped - 93 lines]