Copy only some existing worksheets to a new workbook

  • Thread starter Thread starter munt via OfficeKB.com
  • Start date Start date
M

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
 
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 Exceluserforeman,
for intNum=1 to intCount-2
'minus 2 because of those 2 you do not want
Click to expand...

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:
 
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,
Click to expand...
[quoted text clipped - 51 lines]
Sheets(Array(worksheet_to_copy)).Select
Sheets(Array(worksheet_to_copy)).Copy
Click to expand...
Click to expand...
 
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 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,
Click to expand...
[quoted text clipped - 93 lines]
Click to expand...
 
Back
Top