Select all sheets and copy

[Guest]

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?

 

[Guest]

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

 

[Don Guillett]

why not save the workbook as the new name.

 

[Brian Taylor]

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

or

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

 

[Guest]

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

 

[Dave Peterson]

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

Sheets.copy

 

[Brian Taylor]

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"

 

[Guest]

If you want all the sheets copied

Sheets.Copy

 

[Guest]

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.