Copy Sheets

[poleary2000]

I am using the following code to copy a worksheet:

ActiveSheet.Copy Before:=Sheets("Main Menu")

It is extremely slow in doing so. Is there better code I can be using to speed things up? I already have screen updating and calculation turned off.

 

[Al Chara]

Does the worksheet that you are copying contain a lot of data?

 

[poleary2000]

No not a tremendous amount. Last column used is AB. Last row used is 50. Should I maybe try deleting all of the blank rows and columns. There are about 4 buttons on the sheet however.

 

[Al Chara]

As a rule of thumb, the less amount of formatting that you have to copy the faster it will be.

If there weren't any buttons I was going to ask you to try the following (not sure if it is faster though):

Sheet2.Range(Sheet1.UsedRange.Address).Value = Sheet1.UsedRange.Value

 

[poleary2000]

The new sheet is not there until I copy the old one. I do not have any extra sheets to copy into.

 

[Al Chara]

Sheets.Add will add a blank sheet.

 

[poleary2000]

Thanks for all of the help so far. I would like to try first adding the blank sheet then setting its values equal to the other sheet.

However, how do I deal with the buttons?

 

[Al Chara]

The following will do it:

ActiveSheet.Shapes.SelectAll
Selection.Copy
Sheet2.Activate
ActiveSheet.Paste

But it won't keep them in the same place.

 

[poleary2000]

Thanks.