copying of a sheet to a new workbook

[ashleywanless]

I have some code which i use to export a sheet to a new workbook, its a summary reporting sheet. It works fine but im wondering if i can improve this.

At the moment the issue is the code has to reference the new workbook to copy the data (fields have more than 255 characters). Excel adds 1 to each new workbook that opens so i can only copy the sheet once before i get a debug as the second time the new worksheet is in Book2.

Is it possible to get around this by restructuring the code to not reference the new book? I'm thinking this could be done by using the string function to refer to the book as a variable? Any help would be great.

 

[VoG]

It might help if you posted the code

 

[ashleywanless]

Whoops

Code is

Sheets("Export Report").Select
Sheets("Export Report").Copy
Windows("Open Cases.xls").Activate
Cells.Select
Range("A1").Activate
Selection.Copy
Windows.("Book1").Activate
Cells.Select
Range("A1").Activate
ActiveSheet.Paste
Sheets("Export Report").Select