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.
"c mateland" wrote:
> 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
>
> On Mar 18, 10:03 pm, "c mateland" <chuckmatel...@yahoo.com> wrote:
> > 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
> >
> > On Mar 18, 7:20 pm, Vergel Adriano
> >
> >
> >
> > <VergelAdri...@discussions.microsoft.com> wrote:
> > > 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
>
>
|
Similar Threads
