Syntax error in my modified code. Trying to compile data from multiple workbooks into master workbook.

[j2mbs]

Hello Wizards

I have hit a bump in my quest to compile data from multiple files into 1 master file and am deferring to you for help. I am a VBA novice at best but am generally ok at modifying code I find in online searches but this one has me stumped. Before I show my specific problem I will elaborate on exactly what I am trying to do in case it helps you in helping me.

I have multiple “*FIT.xlsm” files which contain a common worksheet (“FIT Snapshot”), and am trying to create a macro that will:

1. Open through each “*FIT.xlsm” file in the specified folder
2. Copy cells A2:BU12 from the “FIT Snaphot” worksheet in each “*FIT.xlsm” file
3. Paste special value data into the next available row of the “Data Dump” worksheet within my master file

Further macro details that I have yet to add to the code below are:

1. Upon opening each *FIT.xlsm file:
   1. Turn off auto calc
   2. Copy cell D1 from the “Data Dump” worksheet in my master file
   3. Paste Special Values into Cell D1 of the “FIT Snaphot” worksheet in the *FIT.xlsm file
   4. Calculate the “FIT Snaphot” worksheet so all formulas on this worksheet are updated
2. Copy cells A2:BU12 from the “FIT Snaphot” worksheet
3. Paste values into the next available row of the “Data Dump” worksheet within my master file
4. FYI Row 1 is the header row so the 1^st data set would be pasted into row A2.
5. The macro would then loop through all “*FIT.xlsm” files within the folder to ensure all data is captured
6. At end of macro a copy of the updated master file would be saved in the same folder designated to locate all of the “*FIT.xlsm” files with a time/date stamp in the file description.

I have modified the below to open all the files, copy the data, and paste into my master but I am getting a syntax error once Sub CopyData is being called to run. I’m sure this is a simple fix but unfortunately my limited VBA knowledge is hindering my progress.

Sub PullDataDump()

    Dim wbTracker As Workbook
    Dim shtData_Dump As Worksheet
    Dim shtFIT_Snap As Worksheet
    Dim strFilePath As String
    Dim strPath As String


    ' Initialize some variables and
    ' get the folder path that has the files
    Set shtData_Dump = ThisWorkbook.Sheets("Data Dump")
    strPath = GetPath


    ' Make sure a folder was picked.
    If Not strPath = vbNullString Then


        ' Get all the files from the folder
        strfile = Dir$(strPath & "*FIT.xlsm", vbNormal)


        Do While Not strfile = vbNullString


            ' Open the file and get the source sheet
            Set wbTracker = Workbooks.Open(strPath & strfile)
            Set shtFIT_Snap = wbTracker.Sheets("FIT Snapshot")




            'Copy the data
            Call CopyData(shtFIT_Snap, shtData_Dump)


            'Close the workbook and move to the next file.
            wbTracker.Close False
            strfile = Dir$()
        Loop
    End If


End Sub


' Procedure to copy the data.
Sub CopyData(ByRef shtFIT_Snap As Worksheet, shtData_Dump As Worksheet)


    Const strRANGE_ADDRESS As String = "A2:BU12"


    Dim lRow As Long


    'Determine the last row.
    lRow = shtData_Dump.Cells(shtData_Dump.Rows.Count, 1).End(xlUp).Row + 1


    'Copy the data.
    shtFIT_Snap.Range(strRANGE_ADDRESS).Copy
    shtData_Dump.Cells(1Row,1).PasteSpecial xlPasteValuesAndNumberFormats


    ' Reset the clipboard.
    Application.CutCopyMode = xlCopy


End Sub




' Fucntion to get the folder path
Function GetPath() As String


    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select a folder"
        .Title = "Folder Picker"
        .AllowMultiSelect = False


        'Get the folder if the user does not hot cancel
        If .Show Then GetPath = .SelectedItems(1) & "\"


    End With


End Function

Any help/suggestions you folks may have is greatly appreciated.

Thank you again

 

[Ken Cowen]

Rather than opening the file, I usually create a temporary link to the data in the closed files with an array formula, then convert to values. I think the code below will be a lot more efficient for you. You will probably want to put some of your error checking back in but it will do what you described in the first section of your post. You can simplify, and generalize your code a lot if you use range names in your *fit files.

Sub PullDataDump()


Dim sh As Worksheet
Dim n As Integer
Dim fn As String

Set sh = ThisWorkbook.Sheets("Data Dump")

fn = Dir(ThisWorkbook.Path & "" & "*fit.*")

n = 1

Do While Not fn = vbNullString

sh.Cells(n, 1).Resize(11, 73).FormulaArray = "='[" & fn & "]Fit Snapshot'!R1C1:R11C73"

n = n + 11

fn = Dir()

Loop


sh.Cells(1, 1).Resize(n - 1, 73).Copy
sh.Cells(1, 1).PasteSpecial Paste:=xlPasteValues


End Sub

I hope this helps.

Ken

 

[j2mbs]

Ken

Thank you for taking the time to help me out.

Unfortunately I will need to open the workbooks to ensure the proper date is being used in the Fit Snapshot tab and the tab is being calculated to produce desired values. I assume this critical step is not possible without opening the workbooks first. There will be multiple users updating the *FIT.xlsm files, and the number of files will vary from month to month so I need to make sure I can control what data is being pulled.

I will definitely keep your code in my back pocket for future use. I like the idea of being able to pull data without opening the files. Very cool!!

Thank you again.

Jason

 

[Ken Cowen]

You definitely can't do that stuff without opening the workbook; but, you may be better off assuring that is done when the *Fit* workbook is closed, by putting something in the workbook close event of the *Fit* workbooks.
Good luck with your project.
Ken

 

[j2mbs]

Thanks again Ken

With some more searching and re-tooling I have been able to fix my issue, and add all but 1 feature I need. I think I am on the right track.