The following code will copy Module1 from the workbook containing the code to Book3.xls, by exporting it to a file: (John Green, MVP, 2000-12-16, programming <3A3AA258.86E6AB45@rcn.com>)ThisWorkbook.VBProject.VBComponents("Module1").Export "Test.bas" Workbooks("Book3.xls").VBProject.VBComponents.Import "Test.bas" Kill "Test.bas"
Edit -->Move or Copy Sheet.. (Worksheets but not code modules after Excel 95)
The following would be is equivalent to manually
Edit, Move or Copy, [check] Make a Copy, (move to end)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.
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.
213548 - XL2000: "255 Characters in Cell" Error When Copying Worksheet
Compare to macro I created below, neither will process multiple sheets in a selection.
To overcome this possibility of a problem a complete macro is shown below.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 '-- following code from MS KB 213548 -- 'Clear out the clipboard and select cell A1. Application.CutCopyMode = False Range("A1").Select End SubTest data included formula in G2 of =LEN(F2) where F2 contained more than 255 characters
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved