• 1

• 33
• 1
• 4

• 33
• 1
• 4

I posted on the Stack Overflow. They recommended me to post on here

So the whole scope of this Excel file is to copy and paste from other 27 external files to the current Excel file one by one. to show what I mean, following are the code examples and a stimulated capture picture.enter image description here

Macros(line # including space line):

1. Importing Sub

In my file, I have 27 subs like this. It's longer than this example. My real macro has 179 lines as the total. In this example, it only has 51 lines.

The only thing will be changed is the row numbers as the word row in VBA code in line 6.

 Sub Import_NJ()
 Dim Row As Integer, PathFileOpen As String, NameFileOpen As String, 
 TypeFileOpen As String, FullFileName As String, TabCopy As String, ModelFileName As String
 Let Row = Worksheets("Control_Table").Cells("2", "D").Value
 Let PathFileOpen = Worksheets("Control_Table").Cells(Row, "A").Text
 Let NameFileOpen = Worksheets("Control_Table").Cells(Row, "B").Text
 Let TypeFileOpen = Worksheets("Control_Table").Cells(Row, "C").Text
 Let FullFileName = PathFileOpen & "\" & NameFileOpen & TypeFileOpen
 Let TabCopy = Worksheets("Control_Table").Cells(Row, "J").Text
 Let ModelFileName = Worksheets("Control_Table").Cells("10", "B").Text
 
 Application.AskToUpdateLinks = False
 Application.DisplayAlerts = False
 Application.Calculation = xlCalculationManual
 Workbooks.Open FileName:=FullFileName, UpdateLinks:=0
 'Copy Income Statement
 Workbooks(NameFileOpen).Worksheets("Total_Reports").Cells("9", "C").Resize(5, 120).Copy 'Revenues
 Workbooks(ModelFileName).Worksheets(TabCopy).Cells("4", "AW").Resize(5, 120).PasteSpecial xlPasteValues
 Workbooks(NameFileOpen).Worksheets("Total_Reports").Cells("18", "C").Resize(4, 120).Copy 'Prod Costs
 Workbooks(ModelFileName).Worksheets(TabCopy).Cells("11", "AW").Resize(4, 120).PasteSpecial xlPasteValues
 Workbooks(NameFileOpen).Worksheets("Total_Reports").Cells("25", "C").Resize(26, 120).Copy 'Employee Related thru maintenance
 Workbooks(ModelFileName).Worksheets(TabCopy).Cells("17", "AW").Resize(26, 120).PasteSpecial xlPasteValues
 Workbooks(NameFileOpen).Worksheets("Total_Reports").Cells("53", "C").Resize(3, 120).Copy 'D&A
 Workbooks(ModelFileName).Worksheets(TabCopy).Cells("46", "AW").Resize(3, 120).PasteSpecial xlPasteValues
 Application.CutCopyMode = False
 Workbooks(NameFileOpen).Close
 Application.DisplayAlerts = True
 End Sub

2. Batch Import Sub

although it only shows 7 callings, I have 27 calling in my file

 Sub batch_import()
 With Application
 Call Import_NJ 
 Call Import_MD 
 Call Import_PA 
 Call Import_OKC 
 Call Import_CA 
 Call Import_HI 
 Call Import_IN 
 End With
 Application.Calculation = xlCalculationAutomatic
 ActiveWorkbook.Save
 Application.DisplayAlerts = True
 MsgBox _
 ("Batch loading Completed.")
 End Sub

What I tried:

1. Turn off the automatic calculation in each Sub, as you see in the first example Macro. and also others application as many as I could.

2. I didn't shut down the screen updating since my manager wants to see it.

3. I activate the automatic calculating at the end of the Patch sub.

I guess the reseason slowing down the whole process is that I have more than 27 subs in the module.Also, there are a bunch of formulas filled in the worksheets.

Are there any ways to speed up the Macro regarding opening the file and running it? Let me know if I need to elaborate more on this question. Thanks for in advance and read through my question. : )