Copy one whole worksheet into another whole worksheet taking too long

[pincivma]

I am copying one whole worksheet into another worksheet but it is taking way too long and excel displays a "Excel not responding". Here is my code

Sheets("Sheet1") .Select
Cells.Select
Selection.Copy
Sheets("Sheets2").Select
Range("A1").Select
ActiveSheet.Paste

I have used this code many times and it is pretty fast. But now, since I have a lot of formulas and formatting on Sheet1 it is taking forever or I get an"Excel not responding" and I have to abort. Is there a better or faster code that I can use?? I have tried only copying the data only and not the whole sheet but then Excel gives me a warning that I cannot copy and paste since the copy and paste areas are not the same. Please help!!!

 

[Joe4]

What if you temporarily turn off screen updating and calculations while copying, i.e.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Sheet1").Select
Cells.Copy

Sheets("Sheets2").Select
Range("A1").Select
ActiveSheet.Paste

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[dmt32]

Hi,
try

Application.Calculation = xlCalculationManual
Sheets("Sheet1").UsedRange.Copy Sheets("Sheets2").Range("A1")
Application.Calculation = xlCalculationAutomatic

Dave

 

[pincivma]

Sorry guys. I tested both macros and it still says that Excel (Not responding) and after 3 or 4 minutes of waiting, I have to abort the program. I don't know what else to do.

 

[Joe4]

How many formulas are we talking about?
What kind of formulas are they?

 

[pincivma]

The formulas range from cell Q11 to cell CQ383 with only a few gaps in between formulas. The formulas are very simple ones such as Sum and multiplication.

 

[Joe4]

So, that is roughly 25000 calculations, times 2 (2 sheets), so 50000 calculations.
Are there are any other sheets or calculations in this workbook?
How about any VBA code?
Or links or hyperlinks to things outside of that particular workbook?

 

[pincivma]

The workbook is 32.6 MB. It is huge. It has 43 worksheets full of data and formulas some of them complex but the worksheets that I'm working with only have simple formulas like I said in my previous posts

 

[Joe4]

The workbook is 32.6 MB. It is huge. It has 43 worksheets full of data and formulas some of them complex but the worksheets that I'm working with only have simple formulas like I said in my previous posts

Sounds like you found the proverbial straw that broke the camels back!

I am guessing that you are probably using Excel like a database, which can be done, but is usually pretty inefficient to do so, and at some point you will probably run into this problem that you are now having. If that is the case, it is better to use a database program that was designed for that type of thing, like Microsoft Access, SQL, MySQL, or Oracle, instead of trying to force Excel to do something it was wasn't designed for.

If that is not possible (for whatever reason), you may want to look at breaking it up into multiple workbooks, and/or seeing if you can archive off some old data.

 

[pincivma]

Thanks for your help. I will try other avenues.