Slow macro to import data from another workbook, can i speed it up?

graduate106

Board Regular
Joined
Jul 14, 2011
Messages
91
We have a very complex spreadsheet at work which includes a lot of macros that hide/unhide various rows/columns/tabs depending on the options selected.

The spreadsheet is a "calculator" type template/spreadsheet so we update it quite often and instead of entering our client's info into the template every time we have an "import" button which essentially opens a previous saved version of the spreadsheet that we have filled in for our client, and it copies all the data from that spreadsheet into the new template. Works very well, however in some cases is very slow, taking up to 20mins to import all of the data!

I'm pretty sure this can probably be speeded up/improved by those in the know on these things. My VBA skills are very basic!

Our VBA is below which as you can see has a lot of copying and pasting values from one spreadsheet to the other. Any help much appreciated!
Thanks
Simon

Code:
Sub ImportData()

Dim OpenFileName As String
Dim wb As Workbook

Application.AskToUpdateLinks = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

OpenFileName = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls; *.xlsm),*.xls;*.xslm")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)

' Code for invalid source
If wb.Sheets("Setup").Range("A1").Value <> "NHS Pension Workbook" Then
wb.Close
MsgBox ("Import failed - invalid data source!")
GoTo End1
End If

' Code for v9.1
If wb.Sheets("Setup").Range("b150").Value = "9.1" Then

ThisWorkbook.Sheets("Setup").Range("f24").Value = wb.Sheets("Setup").Range("f24").Value
'ThisWorkbook.Sheets("Setup").Range("f25").Value = wb.Sheets("Setup").Range("f25").Value
ThisWorkbook.Sheets("Setup").Range("f91").Value = wb.Sheets("Setup").Range("f91").Value
ThisWorkbook.Sheets("Setup").Range("f104").Value = wb.Sheets("Setup").Range("f104").Value
ThisWorkbook.Sheets("Setup").Range("f110").Value = wb.Sheets("Setup").Range("f110").Value
ThisWorkbook.Sheets("Setup").Range("f112").Value = wb.Sheets("Setup").Range("f112").Value
ThisWorkbook.Sheets("Forecast").Range("e103").Value = wb.Sheets("Forecast").Range("e103").Value
ThisWorkbook.Sheets("Forecast").Range("e158").Value = wb.Sheets("Forecast").Range("e158").Value
ThisWorkbook.Sheets("Setup").Range("c4").Value = wb.Sheets("Setup").Range("c4").Value
ThisWorkbook.Sheets("Setup").Range("e4").Value = wb.Sheets("Setup").Range("e4").Value
ThisWorkbook.Sheets("Setup").Range("f4").Value = wb.Sheets("Setup").Range("f4").Value
ThisWorkbook.Sheets("Setup").Range("g4").Value = wb.Sheets("Setup").Range("g4").Value
ThisWorkbook.Sheets("Setup").Range("f6").Value = wb.Sheets("Setup").Range("f6").Value
ThisWorkbook.Sheets("Setup").Range("f10").Value = wb.Sheets("Setup").Range("f10").Value
ThisWorkbook.Sheets("Setup").Range("f12").Value = wb.Sheets("Setup").Range("f12").Value
ThisWorkbook.Sheets("Setup").Range("f14").Value = wb.Sheets("Setup").Range("f14").Value
ThisWorkbook.Sheets("Setup").Range("f16").Value = wb.Sheets("Setup").Range("f16").Value
ThisWorkbook.Sheets("Setup").Range("f18").Value = wb.Sheets("Setup").Range("f18").Value
ThisWorkbook.Sheets("Setup").Range("h18").Value = wb.Sheets("Setup").Range("h18").Value
ThisWorkbook.Sheets("Setup").Range("f20").Value = wb.Sheets("Setup").Range("f20").Value
ThisWorkbook.Sheets("Setup").Range("f22").Value = wb.Sheets("Setup").Range("f22").Value
ThisWorkbook.Sheets("Setup").Range("d29:i89").Value = wb.Sheets("Setup").Range("d29:i89").Value
ThisWorkbook.Sheets("Setup").Range("f94:k94").Value = wb.Sheets("Setup").Range("f94:k94").Value
ThisWorkbook.Sheets("Setup").Range("f96:k96").Value = wb.Sheets("Setup").Range("f96:k96").Value
ThisWorkbook.Sheets("Setup").Range("f98:k98").Value = wb.Sheets("Setup").Range("f98:k98").Value
ThisWorkbook.Sheets("Setup").Range("f100:k100").Value = wb.Sheets("Setup").Range("f100:k100").Value
ThisWorkbook.Sheets("Setup").Range("f102:k102").Value = wb.Sheets("Setup").Range("f102:k102").Value
ThisWorkbook.Sheets("Setup").Range("f106:k106").Value = wb.Sheets("Setup").Range("f106:k106").Value
ThisWorkbook.Sheets("Setup").Range("f108:k108").Value = wb.Sheets("Setup").Range("f108:k108").Value
ThisWorkbook.Sheets("Setup").Range("D117:D148").Value = wb.Sheets("Setup").Range("D117:D148").Value
ThisWorkbook.Sheets("Setup").Range("G117:G148").Value = wb.Sheets("Setup").Range("G117:G148").Value
ThisWorkbook.Sheets("Setup").Range("J27:O27").Value = wb.Sheets("Setup").Range("J27:O27").Value
ThisWorkbook.Sheets("Forecast").Range("d6").Value = wb.Sheets("Forecast").Range("d6").Value
ThisWorkbook.Sheets("Forecast").Range("a76").Value = wb.Sheets("Forecast").Range("a76").Value
ThisWorkbook.Sheets("Forecast").Range("a106:c155").Value = wb.Sheets("Forecast").Range("a106:c155").Value
ThisWorkbook.Sheets("Forecast").Range("h110").Value = wb.Sheets("Forecast").Range("h110").Value
ThisWorkbook.Sheets("Forecast").Range("a161:b180").Value = wb.Sheets("Forecast").Range("a161:b180").Value
ThisWorkbook.Sheets("AA").Range("j12").Value = wb.Sheets("AA").Range("j12").Value
ThisWorkbook.Sheets("AA").Range("j23").Value = wb.Sheets("AA").Range("j23").Value
ThisWorkbook.Sheets("AA").Range("j34").Value = wb.Sheets("AA").Range("j34").Value
ThisWorkbook.Sheets("AA").Range("j47").Value = wb.Sheets("AA").Range("j47").Value
ThisWorkbook.Sheets("AA").Range("j60").Value = wb.Sheets("AA").Range("j60").Value
ThisWorkbook.Sheets("AA").Range("j73").Value = wb.Sheets("AA").Range("j73").Value
ThisWorkbook.Sheets("AA").Range("j86").Value = wb.Sheets("AA").Range("j86").Value
ThisWorkbook.Sheets("AA").Range("j109").Value = wb.Sheets("AA").Range("j109").Value
ThisWorkbook.Sheets("AA").Range("h109").Value = wb.Sheets("AA").Range("h109").Value
ThisWorkbook.Sheets("AA").Range("j131").Value = wb.Sheets("AA").Range("j131").Value
ThisWorkbook.Sheets("AA").Range("j151").Value = wb.Sheets("AA").Range("j151").Value
ThisWorkbook.Sheets("AA").Range("j171").Value = wb.Sheets("AA").Range("j171").Value
ThisWorkbook.Sheets("AA").Range("j191").Value = wb.Sheets("AA").Range("j191").Value
ThisWorkbook.Sheets("AA").Range("j211").Value = wb.Sheets("AA").Range("j211").Value
ThisWorkbook.Sheets("AA").Range("j231").Value = wb.Sheets("AA").Range("j231").Value
ThisWorkbook.Sheets("AA").Range("j251").Value = wb.Sheets("AA").Range("j251").Value
ThisWorkbook.Sheets("AA").Range("j271").Value = wb.Sheets("AA").Range("j271").Value
ThisWorkbook.Sheets("AA").Range("j291").Value = wb.Sheets("AA").Range("j291").Value
ThisWorkbook.Sheets("AA").Range("j311").Value = wb.Sheets("AA").Range("j311").Value
ThisWorkbook.Sheets("AA").Range("j331").Value = wb.Sheets("AA").Range("j331").Value
ThisWorkbook.Sheets("AA").Range("j351").Value = wb.Sheets("AA").Range("j351").Value
ThisWorkbook.Sheets("AA").Range("j371").Value = wb.Sheets("AA").Range("j371").Value
ThisWorkbook.Sheets("AA").Range("j391").Value = wb.Sheets("AA").Range("j391").Value
ThisWorkbook.Sheets("LTA").Range("C10").Value = wb.Sheets("LTA").Range("C10").Value
ThisWorkbook.Sheets("LTA").Range("J20:J42").Value = wb.Sheets("LTA").Range("J20:J42").Value

wb.Close
MsgBox ("v9.1 detected - data successfully imported!")
GoTo End1

Else
wb.Close
MsgBox ("Import failed - can not recognise data!")
GoTo End1
End If

End1:

Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 
Last edited by a moderator:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
PS. We have just "upgraded" to Office365 which has Excel 2016 which has made this problem 10x worse, hence the query now.

From google search it would appear the Application.Screenupdating = False does not work on new Excel so is very slow due to updating screen after every entry?
THanks
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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