Select all sheets and copy
 

I need to create a copy of all sheets but I do not always know the names of
the sheets I'm copying. Here is the line of code I'm using today but I just
found out that one or more sheets may not exists always.

Sheets(Array("Bank " & BankNum & " Analyzed", "Pivot Table", "Bank " &
BankNum & " Delete Codes")).Copy

Is there a way to eliminate the names and just copy all sheets?

Select all sheets and copy
 

Dim bYes as Boolean, v as Variant
Dim i as Long, sh as Worksheet, sh1 as Worksheet
set sh1 = Activesheet
v = Array("Bank " & BankNum & " Analyzed", _
"Pivot Table", "Bank " & BankNum & " Delete Codes")
bYes = True
for i = lbound(v) to ubound(v)
set sh = Nothing
on error Resume Next
set sh = sheets(v(i))
on error goto 0
if not sh is nothing then
sh.Select Replace:=bYes
bYes = False
end if
Next
activeWindow.Selectedsheets.copy
sh1.Select

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

I need to create a copy of all sheets but I do not always know the names of
the sheets I'm copying. Here is the line of code I'm using today but I just
found out that one or more sheets may not exists always.

Sheets(Array("Bank " & BankNum & " Analyzed", "Pivot Table", "Bank " &
BankNum & " Delete Codes")).Copy

Is there a way to eliminate the names and just copy all sheets?

Select all sheets and copy
 

why not save the workbook as the new name.

--
Don Guillett
SalesAid Software

"hshayh0rn" wrote in message
...
I need to create a copy of all sheets but I do not always know the names of
the sheets I'm copying. Here is the line of code I'm using today but I
just
found out that one or more sheets may not exists always.

Sheets(Array("Bank " & BankNum & " Analyzed", "Pivot Table", "Bank " &
BankNum & " Delete Codes")).Copy

Is there a way to eliminate the names and just copy all sheets?

Select all sheets and copy
 

For i = 1 To Worksheets.Count
Sheets(i).Copy Befo=Workbooks("Book2").Sheets(2)
Next wks

or

For each wks in worksheets
wks.copy Befo=Workbooks("Book2").Sheets(2)
Next wks

Select all sheets and copy
 

Thanks Brian. How would I use your statement if I wanted all of the sheets to
be copied to a new workbook?

"Brian Taylor" wrote:



For i = 1 To Worksheets.Count
Sheets(i).Copy Befo=Workbooks("Book2").Sheets(2)
Next wks

or

For each wks in worksheets
wks.copy Befo=Workbooks("Book2").Sheets(2)
Next wks

Select all sheets and copy
 

One more (if you really meant copy all the sheets):

Sheets.copy



hshayh0rn wrote:

I need to create a copy of all sheets but I do not always know the names of
the sheets I'm copying. Here is the line of code I'm using today but I just
found out that one or more sheets may not exists always.

Sheets(Array("Bank " & BankNum & " Analyzed", "Pivot Table", "Bank " &
BankNum & " Delete Codes")).Copy

Is there a way to eliminate the names and just copy all sheets?

--

Dave Peterson

Select all sheets and copy
 

Dave's solution is much more elegant than my long winded solution.

If you are trying to copy all sheets into a new workbook, then I think
Don's earlier question is valid. Why not do a save as?

Workbooks("MyWorkbook").SaveAs "C:\Temp\MySpecialWorkbook.xls"

Select all sheets and copy
 

If you want all the sheets copied

Sheets.Copy

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote:

Thanks Brian. How would I use your statement if I wanted all of the sheets to
be copied to a new workbook?

"Brian Taylor" wrote:



For i = 1 To Worksheets.Count
Sheets(i).Copy Befo=Workbooks("Book2").Sheets(2)
Next wks

or

For each wks in worksheets
wks.copy Befo=Workbooks("Book2").Sheets(2)
Next wks

Select all sheets and copy
 

sheets.copy worked great. I don't want to simply save it as another name
because I need to remove all of the code involved before it is save and I
thought just taking the sheets and copying them to a new workbook solved for
that the cleaniest.

"Dave Peterson" wrote:

One more (if you really meant copy all the sheets):

Sheets.copy



hshayh0rn wrote:

I need to create a copy of all sheets but I do not always know the names of
the sheets I'm copying. Here is the line of code I'm using today but I just
found out that one or more sheets may not exists always.

Sheets(Array("Bank " & BankNum & " Analyzed", "Pivot Table", "Bank " &
BankNum & " Delete Codes")).Copy

Is there a way to eliminate the names and just copy all sheets?

--

Dave Peterson