Speed up Excel Macro b/n copy and paste external files

Question

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.

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. : )

Update:08/15/2017

Because the Excel layout in the previous pic was in order, so Macro could search the last row and work perfectly. But in the real format, it's like this. Any thoughts I could alter it to the for loop?

Answer 1

The slowest part of your macro will be the opening of each Excel spreadsheet file. The copying part will run very quickly. Since the logic of copying from an external workbook to a sheet within the current workbook is consistent, only the locations and filenames are changing, it's easier to create a common method with parameters to normalize what's being done. That's what you'll see in the example below.

First, some general comments:

1. You've got a single long line to declare all your variables. In VBA it's more common to declare each variable on a separate line. The reason is that it's easy to forget to declare a variable type when it's in a long line of Dims. The one you miss will default to Variant, which, while not a bad thing, is not the desired declaration.
2. Always use Option Explicit to force VBA to check all variables for proper declaration.
3. Using the keyword Let is not required to set the value of a variable, however
4. There is a difference between a typed variable (Integer, Long, String, Double, etc) and an object variable (such as Range, Worksheet, Workbook, or a custom class). You only need to use the Set keyword in order to assign a "value" to an object. No keyword is necessary for a simple typed variable.
5. It may seem like more work to type in, but your code becomes cleaner and clearer if you specifically establish clearly named variables for the "source" and "destination". It's easier to keep track of where things are going. In fact, you should make it a habit to always define a variable for the Workbook and for the Worksheet you're operating with.

For the main routine, you don't need to use a column to identify the row number. That can be determined programmatically at run time.

Option Explicit

Sub BatchImport()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Control_Table")
    With ws
        lastRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

        Dim i As Long
        Dim path As String
        Dim filename As String
        Dim ext As String
        Dim tabName As String

        For i = 2 To lastRow
            path = .Cells(i, 1)
            filename = .Cells(i, 2)
            ext = .Cells(i, 3)
            tabName = .Cells(i, 10)
            ImportStateData path, filename, ext, tabName
        Next i
    End With

    wb.Save
    MsgBox "Batch loading completed.", vbInformation + vbOKOnly, "Import Completed"

End Sub

Notice the parameters that are loaded from the columns on your Control_Table and how they're passed to the working Sub. The ImportStateData sub accepts those parameters to change where the data comes from and goes to.

The biggest difference from your original code is how the copy-paste is accomplished. Because a copy and a paste is a notion from the manual actions a user would take to get the data into their destination, it's natural to want to use that function in your VBA. It's really not necessary, especially when you set up the ranges for the source and destination as shown here.

Private Sub ImportStateData(ByVal path As String, _
                            ByVal filename As String, _
                            ByVal ext As String, _
                            ByVal tabName As String)
    Dim thisWB As Workbook
    Dim destWS As Worksheet
    Set thisWB = ThisWorkbook
    Set destWS = thisWB.Sheets(tabName)

    Dim stateWB As Workbook
    Dim stateWS As Worksheet
    Set stateWB = Workbooks.Open(path & "\" & filename & ext, _
                                 UpdateLinks:=False, _
                                 ReadOnly:=True)
    Set stateWS = stateWB.Sheets("Total_Reports")

    Dim stateRevenues As Range
    Dim stateProdCosts As Range
    Dim stateEmplMaint As Range
    Dim stateDA As Range
    Set stateRevenues = stateWS.Range("C9").Resize(5, 120)
    Set stateProdCosts = stateWS.Range("C18").Resize(4, 120)
    Set stateEmplMaint = stateWS.Range("C25").Resize(26, 120)
    Set stateDA = stateWS.Range("C53").Resize(3, 120)

    Dim destRevenues As Range
    Dim destProdCosts As Range
    Dim destEmplMaint As Range
    Dim destDA As Range
    Set destRevenues = destWS.Range("AW4").Resize(5, 120)
    Set destProdCosts = destWS.Range("AW11").Resize(4, 120)
    Set destEmplMaint = destWS.Range("AW17").Resize(26, 120)
    Set destDA = destWS.Range("AW46").Resize(3, 120)

    destRevenues.Value = stateRevenues.Value
    destProdCosts.Value = stateProdCosts.Value
    destEmplMaint.Value = stateEmplMaint.Value
    destDA.Value = stateDA.Value

    stateWB.Close

End Sub

So, for convenience here's the whole module in a single block:

Option Explicit

Sub BatchImport()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lastRow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Control_Table")
    With ws
        lastRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

        Dim i As Long
        Dim path As String
        Dim filename As String
        Dim ext As String
        Dim tabName As String

        For i = 2 To lastRow
            path = .Cells(i, 1)
            filename = .Cells(i, 2)
            ext = .Cells(i, 3)
            tabName = .Cells(i, 10)
            ImportStateData path, filename, ext, tabName
        Next i
    End With

    wb.Save
    MsgBox "Batch loading completed.", vbInformation + vbOKOnly, "Import Completed"

End Sub

Private Sub ImportStateData(ByVal path As String, _
                            ByVal filename As String, _
                            ByVal ext As String, _
                            ByVal tabName As String)
    Dim thisWB As Workbook
    Dim destWS As Worksheet
    Set thisWB = ThisWorkbook
    Set destWS = thisWB.Sheets(tabName)

    Dim stateWB As Workbook
    Dim stateWS As Worksheet
    Set stateWB = Workbooks.Open(path & "\" & filename & ext, _
                                 UpdateLinks:=False, _
                                 ReadOnly:=True)
    Set stateWS = stateWB.Sheets("Total_Reports")

    Dim stateRevenues As Range
    Dim stateProdCosts As Range
    Dim stateEmplMaint As Range
    Dim stateDA As Range
    Set stateRevenues = stateWS.Range("C9").Resize(5, 120)
    Set stateProdCosts = stateWS.Range("C18").Resize(4, 120)
    Set stateEmplMaint = stateWS.Range("C25").Resize(26, 120)
    Set stateDA = stateWS.Range("C53").Resize(3, 120)

    Dim destRevenues As Range
    Dim destProdCosts As Range
    Dim destEmplMaint As Range
    Dim destDA As Range
    Set destRevenues = destWS.Range("AW4").Resize(5, 120)
    Set destProdCosts = destWS.Range("AW11").Resize(4, 120)
    Set destEmplMaint = destWS.Range("AW17").Resize(26, 120)
    Set destDA = destWS.Range("AW46").Resize(3, 120)

    destRevenues.Value = stateRevenues.Value
    destProdCosts.Value = stateProdCosts.Value
    destEmplMaint.Value = stateEmplMaint.Value
    destDA.Value = stateDA.Value

    stateWB.Close

End Sub