Re: Copying a sheet2 to last sheet in workbook

  • Thread starter Thread starter David McRitchie
  • Start date Start date
D

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
 
Hi Dave - 255 limit still applies in 2002 for that operation.

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