Re: Copying a sheet2 to last sheet in workbook

[David McRitchie]

Hi Todd,
The following would be closer to the result you would get if you
did this manually. ..

ActiveSheet.Copy After:=Sheets(Sheets.Count)

so that AgeTable copied first time shows up at end as AgeTable (2)
and the next time as AgeTable (3)
If you close up the space renaming to AgeTable(3) the copied
sheet will not add the space and will be AgeTable(4)

I always thought you got an exact copy, with
Edit, Move or Copy Sheet, (checkmark) Make a Copy
but in my Excel 2000, received the following warning message when
I manually created the copy. The error occurs but is suppressed when
using a macro. This may or may not affect you, but the following
overcomes that problem.

!! The sheet you are copying has cells that contain more than 255 characters. When you copy the entire sheet, only
!! the first 255 characters in each cell are copied.
!!
!! To copy all of the characters, copy the cells to a new sheet instead of copying the entire sheet.
!!
!! OK

Sub copysheettoend()
Dim wsSource As Worksheet
Dim wsNew As Worksheet
Set wsSource = ActiveSheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Set wsNew = ActiveSheet
'--fixup for cell lengths greater than 255
wsSource.UsedRange.Copy
wsNew.Range("A1").PasteSpecial
Cells.Calculate
End Sub

Test data included formula in G2 of =LEN(F2)
where F2 contained more than 255 characters

The following web pages of mine may accumulate additional information
in the future: sheets.htm copyvba.htm

 

[Ken Wright]

Hi Dave - 255 limit still applies in 2002 for that operation.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

 

[Tom Ogilvy]

for Reference,
http://support.microsoft.com/default.aspx?scid=kb;en-us;213548
XL2000: "255 Characters in Cell" Error When Copying Worksheet


I didn't immediately see an article that talks about xl2002.