Improving Macro Run Speed

[MJH2014]

Hi,

I have built a relatively large macro that I am now breaking down into chunks to re-edit and improve my run time. I am trying to write a code that will copy all cells in tabs from a saved workbook and paste them into new tabs in the active workbook (which will have a different name each time I run this). Even a code this simple is triggering a lot of lag and "(Not Responding") in Excel. I am running a HP EliteBook 850 G1 Notebook with a Intel Core i7-4600U which I thought should have enough processing power to handle these types of commands, but when I monitor the CPU usage history, when I run the macro it is maxing it out. Anyone know how I can run this type of code more efficiently?

I have copied the code I am running below:

Sub CopySheets()
ScreenUpdating = False
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:= _
"C:\Users\mhoss\Desktop\Template Run with Inv Sum.xls", UpdateLinks _
:=3
Dim wb2 As Workbook
Set wb2 = Workbooks("Template Run with Inv Sum.xls")
wb1.Sheets("test").Activate
wb1.Sheets.Add After:=wb1.Sheets("test")
ActiveSheet.Name = "Investment Summary"
wb2.Sheets("Investment Summary").Activate
Cells.Copy
wb1.Sheets("Investment Summary").Activate
Cells.PasteSpecial
wb1.Sheets.Add After:=wb1.Sheets("Investment Summary")
ActiveSheet.Name = "Portfolio"
wb2.Sheets("Portfolio").Activate
Cells.Copy
wb1.Sheets("Portfolio").Activate
Cells.PasteSpecial
ScreenUpdating = True
End Sub

Thank you to anyone that may be able to help!

 

[btomjack]

My guess is that this is the line that is taking the longest:

[COLOR=#333333][FONT=courier new]Workbooks.Open Filename:= _[/FONT][/COLOR]
[COLOR=#333333][FONT=courier new]"C:\Users\mhoss\Desktop\Template Run with Inv Sum.xls", UpdateLinks _[/FONT][/COLOR]
[COLOR=#333333][FONT=courier new]:=3[/FONT][/COLOR]

Use an apostrophe to comment that line out and see how fast it runs. If that line turns out to be your issue and that line is necessary, I don't know what you can do to fix it.

 

[MJH2014]

Thanks for the reply; I commented it out and just had the file open in the background and the CPU usage still maxed out and lagged even though the macro ran clean..

 

[AlphaFrog]

You may find these links useful.

RDBMerge, Excel Merge Add-in for Excel for Windows

Merge data from all workbooks in a folder

 

[MJH2014]

Thank you- that is a really interesting feature I will save for later, but for this specific use, I don't think the merge Add-In will be the right option. Thanks for the replies, I hope that its not my computer, although I don't think it should be an issue

 

[Eric Golf]

Hi,

wb2.Sheets("Investment Summary").Activate
Cells.Copy
wb1.Sheets("Investment Summary").Activate
Cells.PasteSpecial

Generally you should avoid Activate / Select code as this slows things down. These lines could be shortened to

wb2.Sheets("Investment Summary").Cells.Copy
wb1.Sheets("Investment Summary").Cells.PasteSpecial

You are using PasteSpecial but not specifying what e.g. Values, formats etc.? Not sure if this could be an issue

Also you are doing so for all cells in the worksheet, all 17 billion of them, presuming xl2007 or later. Try

wb2.Sheets("Investment Summary").UsedRange.Copy
wb1.Sheets("Investment Summary").Cells(1,1).PasteSpecial

Just suggestions, untested.

Hope this helps,

Eric

 

[MJH2014]

Eric- that is very helpful actually; this is my first time building a financial analysis model using VBA and I have eliminated most of my uses of "Select" because I heard that was time consuming. The resource I used as reference said to replace Select with Activates where I had to and eliminate that type of reference all together when I can as you have suggested. My macro is remarkably slow right now (20 minutes, no joke)" to run 1,700 lines of VBA. I am rebuilding a macro I used at an old company that did the exact same function and ran in 5 seconds. Do you think the "Activate" uses throughout may be a source of inefficiency? I have turned off screen updating and set calculations to manual and it did not help. Thanks for your help!

 

[jimrward]

does the data you are copying have formulae or is it pure data

try turning off the calculation at the start of the routine and setting it back on at the end

 

[Eric Golf]

Hi MJH2014,

It is difficult to tell with a small sample out of 1700 lines but generally, as you say, Select & Activate should be avoided. Possibly you could put some lines in that print the time out to a blank sheet at various points in your code so you can tell which part is taking the time. This would then allow you to focus on the bit that takes 19 out of the 20 minutes

Eric

 

[AlphaFrog]

This will copy the template sheet instead adding a blank sheet and copying the cells from the template.

[COLOR=darkblue]Sub[/COLOR] CopySheets()
    
    [COLOR=darkblue]Dim[/COLOR] wb1    [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] wb2    [COLOR=darkblue]As[/COLOR] Workbook
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wb1 = ActiveWorkbook
    
    [COLOR=darkblue]Set[/COLOR] wb2 = Workbooks.Open(Filename:= _
        "C:\Users\mhoss\Desktop\Template Run with Inv Sum.xls", UpdateLinks:=3)
    
    [COLOR=green]'Copy template worksheet[/COLOR]
    wb2.Sheets("Investment Summary").Copy After:=wb1.Sheets("test")
    
    [COLOR=green]'Create and name a new blank worksheet[/COLOR]
    wb1.Sheets.Add(After:=wb1.Sheets("Investment Summary")).Name = "Portfolio"
    
    [COLOR=green]'Close Template workbook[/COLOR]
    wb2.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note: in your original code you had this; ScreenUpdating = False
The correct syntax to suspend screen updating is this; Application.ScreenUpdating = False

If you're running this code multiple times for one session, you nay not want to Open\Close the Template workbook each time. Just open it once and leave it open as long as you need. That could be a significant time saver.