Copy one whole worksheet into another whole worksheet taking too long

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203
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!!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
What if you temporarily turn off screen updating and calculations while copying, i.e.
Code:
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
 
Last edited:
Upvote 0
Hi,
try

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

Dave
 
Upvote 0
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.
 
Upvote 0
How many formulas are we talking about?
What kind of formulas are they?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top