Copy worksheets to another workbook

[c mateland]

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

 

[Vergel Adriano]

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]

Seems to work. Thanks, Vergel!! With this method, I didn't get the
255 character problem, and it copied the hidden sheets while keeping
them hidden in the destination book. And it brought over the named
ranges. Just like I wanted.

But, just for my knowledge, can you tell me the purpose of setting the
wbTarget back to nothing?

Thanks,
Chuck

 

[c mateland]

No, I spoke too soon. <g> It still has the 255 character issue. All
cells with more than that got truncated. It just didn't alert me to
it.

So, how does one get around this challenge? I need to bring the
worksheets over to ThisWorkbook, but I need all the cell contents
without anything getting truncated.

-Chuck

 

[Vergel Adriano]

Chuck,

I missed the 255 character limit. I think the only way around the issue is
to copy the cells to the worksheet instead of copying the entire sheet. So
what I did was after copying all sheets, do another copy of the cells from
the source sheet to the target. It's redundant, but is a quick fix to the
code I originally posted. Try it and perhaps it's something that will work
for your situation. Note that this will work only when the source and target
workbooks don't have sheets with the same name. Because if there are sheets
with the same name, Excel will rename the sheet in the target and the
following UsedRange copy operation will paste the cells in the wrong sheet.

Sub CopyAllSheets()

Dim wbTarget As Workbook
Dim sht As Worksheet
Set wbTarget = Workbooks.Open("D:\Book1.xls")
ThisWorkbook.Sheets.Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
For Each sht In ThisWorkbook.Worksheets
sht.UsedRange.Copy wbTarget.Sheets(sht.Name).UsedRange
Next sht
wbTarget.Close True
Set sht = Nothing
Set wbTarget = Nothing

End Sub


The reason for setting the objects back to Nothing is to make sure the
memory allocated to the objects are released. It's also a good practice to
set objects to Nothing when you're done with them to avoid (and sometimes
find) bugs in the code.