Move, Copy, Replace, Populate

Location: http://www.mvps.org/dmcritchie/excel/copyvba.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Copy Module from one workbook to another (#CopyModule)

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"

Copy Worksheet from one workbook to another

Edit -->Move or Copy Sheet.. (Worksheets but not code modules after Excel 95)

Copy Worksheet to End (#copyws)

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.

[OK]

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 Sub
Test data included formula in G2 of =LEN(F2) where F2 contained more than 255 characters

Programming To The Visual Basic Editor, Chip Pearson (cpearson.com) on his site. Coding to make use of clipboard. On my site make use of this see macros MakeHTML_link on my Build Table of Contents (buildtoc.htm) page.
Worksheets in VBA Coding and in Worksheet Formulas (sheets.htm)

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on December 16, 2000.

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

Move, Copy, Replace, Populate

Copy Module from one workbook to another (#CopyModule)

Copy Worksheet from one workbook to another

Copy Worksheet to End (#copyws)

Related