In VBA, you can copy sheets using the Copy method. For instance, to place a copy of the first worksheet at the end of the workbook, you can use a statement like this:
Worksheets(1).Copy After:=Sheets(Sheets.Count)
You can also copy sheets to different workbooks. This example copies a worksheet from Book1 and puts the copy into Book2:
Workbooks(“Book1?).Worksheets(1).Copy After:=Workbooks(“Book2?).Sheets(1)
The Copy method has two arguments, Before and After, which identify the location of the new sheet. The most you can specify is one, but you can also specify none, like in this example
Worksheets(1).Copy
By omitting both arguments, Excel will create a new workbook that contains the copied sheet. Did you ever want to send someone a spreadsheet, but didn’t want them to see everthing in it? This is a good way to isolate information into another workbook.
I notice that, when copying sheets in Excel 2002, I get a warning if any of the cells contains more than 255 characters. Excel tells me that the Copy Worksheet command will only copy the first 255 characters in a cell and that, if I want to copy the entire contents of such a cell, I should copy the cells instead of the worksheet.
Does the VBA version of Copy Worksheet have a similar limitation?
Toad: That’s correct. I haven’t tested it in 2003 yet, but I’ll bet it hasn’t changed. In my RangeToHTML function on http://www.dicks-clicks.com/excel/olSending.htm#ConvertingExceltoHTML, I copy the sheet, then copy the values. It’s really a stupid limit that I hope they fix in future versions.