Macro to Pull Summary from Multiple Files

[evxret]

Hi All,

I am not very good with VBA, but I am trying to make a macro to complete the following.
1. Select a folder that contains any number of excel files inside
2. Go through the files inside of the selected folder, pull their company name (which in my case is populated in column B, so just pulling the value of B2)
3. Pull the Total payout from the files (Which is the total row (last row) of column 33 or AG)
4. Paste Company name, and Total payout in the Summary worksheet, contained inside of the macro's file
5. Continue looping through files in my selected folder until I have a summary of 15-20 company names + their statement totals.

Here is the code I have thus far, which is a combo of stuff I pulled from YouTube & my own knowledge of VBA - I am getting Error 438 'Object Doesnt support this method' (sh.Cells(i, 1) = ActiveWorkbook.Cells(2, 2).Text)

2 Questions basically:
- What am I doing wrong in this code to receive this error?
- Would it be possible to pull the SUM of another column and paste it into a third cell of my summary? (Example of my ideal summary: Column A = Company Name, B = Payout, C = Total Commission) The Total Commission would come from pulling the SUM of column E on each file, there is not a total at the bottom like there is for my total payout data.

Let me know if there are any questions and I greatly appreciate the help I receive on this forum. Thanks all

Sub grab_data_from_files_in_folder()

Dim myPath As String
Dim myFile As String
Dim FldrPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
''Pick the Folder to Loop through
Set sh = ThisWorkbook.Sheets("Summary")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Please Select Folder"
        .AllowMultiSelect = False
        .ButtonName = "Confirm"
        If .Show = -1 Then
            myPath = .SelectedItems(1) & "\"
        Else
            End
        End If
    End With
''Set the Column Headers on my macro/summary file
With sh
    .Cells.ClearContents
    .Cells(1, 1) = "PartnerName"
    .Cells(1, 1).Font.Size = 14
    .Cells(1, 1).Font.Bold = True
    .Cells(1, 2) = "PartnerPayout"
    .Cells(1, 2).Font.Size = 14
    .Cells(1, 2).Font.Bold = True

End With
''Loop through the path I selected and pull B2 value (company) & last row of column 33
myFile = Dir(myPath)
i = 2

Do While myFile <> ""

    Workbooks.Open Filename:=myPath & myFile
    
    sh.Cells(i, 1) = ActiveWorkbook.Cells(2, 2).Text
    sh.Cells(i, 2) = ActiveWorkbook.Cells(Rows.Count, 33).End(xlUp).Value
    
    ActiveWorkbook.Close savechanges:=False
    
    myFile = Dir
    i = i + 1

Loop


Application.ScreenUpdating = True


End Sub

 

[dreid1011]

What happens if you try this:

sh.Cells(i, 1) = ActiveWorkbook.Range("B2").Text

 

[dreid1011]

And possibly (untested):

sh.Cells(i, 3) = WorksheetFunction.Sum(ActiveWorkbook.Range("E2:E"&Rows.Count).End(xlUp).Row)