Copy one whole worksheet into another whole worksheet taking too long

pincivma

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,157
Office Version
2013
Platform
Windows
Hi,
try

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

pincivma

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
How many formulas are we talking about?
What kind of formulas are they?
 

pincivma

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,078,487
Messages
5,340,633
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top