How could I tweak the code below that will copy all sheets from my
ActiveWorkbook EXCEPT for sheets A;B and C?

Code below will copy 2 specified sheets, but I want to twist this
around as I have a large number to copy and don't want to hard code
them as below

Set Sourcewb = ActiveWorkbook
Sourcewb.Sheets(Array("Header", "Order")).Copy
Set Destwb = ActiveWorkbook

Sub CopyBook()

First = True
For Each Sht In ThisWorkbook.Sheets
Select Case Sht.Name

Case "A", "B", "C"
'Do Nothing
Case Else
If First = True Then
'Create New workbook
Sht.Copy
Set NewBk = ActiveWorkbook
First = False
Else
With NewBk
Sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
End Select
Next Sht

End Sub


"Seanie" wrote:

How could I tweak the code below that will copy all sheets from my
ActiveWorkbook EXCEPT for sheets A;B and C?

Code below will copy 2 specified sheets, but I want to twist this
around as I have a large number to copy and don't want to hard code
them as below

Set Sourcewb = ActiveWorkbook
Sourcewb.Sheets(Array("Header", "Order")).Copy
Set Destwb = ActiveWorkbook

Thanks Joel, are the sheet names case sensitive within the code?

The test using strings ze case sensitive

from
Select Case Sht.Name
to
Select Case Ucase(Sht.Name)

The make sure the name in this statement is all capital

Case "A", "B", "C"


"Joel" wrote:


Sub CopyBook()

First = True
For Each Sht In ThisWorkbook.Sheets
Select Case Sht.Name

Case "A", "B", "C"
'Do Nothing
Case Else
If First = True Then
'Create New workbook
Sht.Copy
Set NewBk = ActiveWorkbook
First = False
Else
With NewBk
Sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
End Select
Next Sht

End Sub


"Seanie" wrote:

How could I tweak the code below that will copy all sheets from my
ActiveWorkbook EXCEPT for sheets A;B and C?

Code below will copy 2 specified sheets, but I want to twist this
around as I have a large number to copy and don't want to hard code
them as below

Set Sourcewb = ActiveWorkbook
Sourcewb.Sheets(Array("Header", "Order")).Copy
Set Destwb = ActiveWorkbook

Great, I got it to work as below. Finally how could I place a Msg Box
pop up, if there are no sheets to copy, i.e. the only sheets that are
in the source workbook are A,B,C,D?


First = True
For Each sht In ThisWorkbook.Sheets
Select Case sht.Name


Case "Header", "A", "B", "C", "D"
'Do Nothing
Case Else
If First = True Then
'Create New workbook
sht.Copy
Set Destwb = ActiveWorkbook
First = False
Else
With Destwb
sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
End Select
Next sht

Add and IF statement at the bottom like below.

First = True
For Each sht In ThisWorkbook.Sheets
Select Case sht.Name


Case "Header", "A", "B", "C", "D"
'Do Nothing
Case Else
If First = True Then
'Create New workbook
sht.Copy
Set Destwb = ActiveWorkbook
First = False
Else
With Destwb
sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
End Select
Next sht

If First = True then
msgbox("No sheets found to copy")
End IF

"Seanie" wrote:

Great, I got it to work as below. Finally how could I place a Msg Box
pop up, if there are no sheets to copy, i.e. the only sheets that are
in the source workbook are A,B,C,D?


First = True
For Each sht In ThisWorkbook.Sheets
Select Case sht.Name


Case "Header", "A", "B", "C", "D"
'Do Nothing
Case Else
If First = True Then
'Create New workbook
sht.Copy
Set Destwb = ActiveWorkbook
First = False
Else
With Destwb
sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
End Select
Next sht

Thanks, It debugs with message "Copy Method of Worksheet class failed"
on text

sht Copy

This is when there are no sheets apart from A,B,C,D. If I have a sheet
other than those, code works fine