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!

 

[MJH2014]

Perfect- hey thanks again for all the help. This is my first big VBA project and am learning a ton on the way (including discovering this forum as a great resource).

Take Care,

Mitch

 

[AlphaFrog]

Perfect- hey thanks again for all the help. This is my first big VBA project and am learning a ton on the way (including discovering this forum as a great resource).

Take Care,

Mitch

You're welcome Mitch.

Take Care,
Mitch