Summing column H in 100 unopened workbooks

[jardenp]

I have two similar challenges I need help with:

1. I have about a hundred workbooks saved in a folder. I need to get the sum of all H columns in those workbooks. How can I do this without opening them all and using something like this:

=SUM('[Workbook1.xls]Sheet1'!H:H, '[Workbook2.xls]Sheet1'!H:H, ...)
​

I'm guessing there is a VBA script to do this, preferably one that can automatically perform the action on all .xls files in a folder, so I don't need to type in each workbook name.

2. I have some workbooks, each with about 75 worksheets. I need to get the sum of each B3 cell in a workbook. I will do this on a new sheet within each workbook, so the solution doesn't need to reference workbook names. However, it can't specifically reference sheet names either because each workbook is going to have different, unpredictable sheet names.

If the solution to challenge 1 isn't sheet-name specific, i.e., it automatically includes all sheets in a workbook, and not, say, just sheets with a specific name like "Sheet1", then the same solution should work for both challenges.

Thank you for any help you provide. The thought of having to do this manually makes me want to go wander the train tracks next to my office blindfolded!

 

[jardenp]

Hold up! If I run the script in the VBA editor, the results appear on the sheet. Why won't this run as a normal macro?

snb, thanks for the code. It works perfectly (since I figured out my end! I'm a noob is many ways). I'm just curious about why running the macro doesn't work.

 

[prabby25101981]

Using my first macro, you get the names of the files in FileNames sheet. Then run this code to get totals from H column of these sheets.

Sub GetRelevantData()
Dim LRowFiles, LRowVals, TotValuesH As Long
Dim MainWB, CopyWB As Workbook
Dim Pth As String
Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "FinalValues"
Pth = InputBox("Enter the path where files are saved : ")
Set MainWB = ActiveWorkbook
LRowFiles = MainWB.Sheets("FileNames").Range("A65536").End(xlUp).Row
TotValuesH = 0
For i = 2 To LRowFiles
    Workbooks.Open (Pth & "\" & MainWB.Sheets("FileNames").Range("A" & i).Value)
    Set CopyWB = ActiveWorkbook
    LRowVals = CopyWB.Sheets("Sheet1").Range("H65536").End(xlUp).Row
    For j = 2 To LRowVals
        TotValuesH = TotValuesH + CopyWB.Sheets("Sheet1").Range("H" & j).Value
    Next
    CopyWB.Close SaveChanges:=False
Next
MainWB.Sheets("FinalValues").Range("A2").Value = TotValuesH
MsgBox "Please check the total of Column H in FinalValues Sheet's A2 cell!"
End Sub

 

[jardenp]

Thanks Prabby. Looks good. I appreciate your time.

 

[snb_]

you should:

- open a new workbook
- open the VBEditor (alt_F11)
- insert a new macro module
- paste the macro in this new module
- open Excel (alt_F11)
- open the macro-display (alt_ F8 )
- doubleclick the macro.

 

[prabby25101981]

Hi Jardenp,

This modified code with add another sheet to all workbooks in the folder (that you mention in the dialog box), and add total of B3 in all sheets in A1 cell of the new worksheet.

Sub GetRelevantData()
Dim LRowFiles, LRowVals, TotValuesH As Long
Dim MainWB, CopyWB As Workbook
Dim Pth As String
Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "FinalValues"
Pth = InputBox("Enter the path where files are saved : ")
Set MainWB = ActiveWorkbook
LRowFiles = MainWB.Sheets("FileNames").Range("A65536").End(xlUp).Row
TotValuesH = 0
For i = 2 To LRowFiles
    Workbooks.Open (Pth & "\" & MainWB.Sheets("FileNames").Range("A" & i).Value)
    Set CopyWB = ActiveWorkbook
    LRowVals = CopyWB.Sheets("Sheet1").Range("H65536").End(xlUp).Row
    For j = 2 To LRowVals
        TotValuesH = TotValuesH + CopyWB.Sheets("Sheet1").Range("H" & j).Value
    Next
    With CopyWB.Sheets.Add
        .Cells(1, 1) = "=SUM(" & Sheets(1).Name & ":" & Sheets(Sheets.Count).Name & "!B3)"
    End With
    CopyWB.Close SaveChanges:=False
Next
MainWB.Sheets("FinalValues").Range("A2").Value = TotValuesH
MsgBox "Please check the total of Column H in FinalValues Sheet's A2 cell!"
End Sub

 

[busby42]

Hi All,

Sorry to resurrect a thread that looks solved - I have a similar problem, but my files are spread across different sub-folders , is there a simple modification to your code that could let me copy whole columns or sheets from multiple files in multiple sub-folders (in one root path though) into one 'master' workbook?

Oh, I would prefer the files to be referenced from a table of filenames included in the 'master' if possible?

I have been looking for a solution for days!

Many thanks
Sam