The code below will copy all sheets from the current workbook to a target
workbook. Excel automatically adjusts the name in the Target workbook if
there will be duplicates. For example, if the name in the source workbook is
"Sheet1" and there already is a "Sheet1" in the target, Excel will rename the
copy as "Sheet1 (2)". Excel also copies the current visibility of the sheets
.. If it's hidden in the source, the copy will be hidden in the target.

Sub CopyAllSheets()

Dim wbTarget As Workbook
Set wbTarget = Workbooks.Open("D:\Book1.xls")
ThisWorkbook.Sheets.Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
wbTarget.Close True
Set wbTarget = Nothing

End Sub



"c mateland" wrote:

Excel 2003

I'm writing a routine that includes copying all worksheets from a
source workbook into a destination workbook that already has some of
its own worksheets.

The source book contains both visible and hidden worksheets. I need
all of them copied to the destination workbook, but, there, they have
to be hidden/visible as they were in the source. The names and
quantities of the sheets in the source may vary, so I need a way to do
this with the source sheet quantity, names, and their hidden status
being variable. Can someone share a good strategy for doing that in
VBA? Some way to inventory and capture names/hidden status and then
copy, maybe?

Also, how do I get around the 255 character limit when copying the
sheets? Is there a better way to bring the sheets over to another
book, like copy all cells instead? Is that more reliable? If I do
that, I also have to reconstruct the range names they used. Any
thoughts on doing that?

Thanks for your input.
-Chuck