Loop through all worksheets, sum certain column of each one and put results into a new sheet

[OscarSuarez27]

Good morning.


I'm new using Macros and have no idea about programming, if anyone could please help me with this I will appreciate it:

1. I receive workbooks from a customer disputing some charges, sometimes the workbooks came with just one sheet, sometimes they came with 20 sheets but they always include a column which first row text is "Credit". I'm needing a code that can l[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]oop through all worksheets in the workbook, sum entire "Credit"

column ​

in each sheet (the first cell always has text "Credit" but depending on customer, row number could vary from 50 to 500 and column could vary from B to W) put results into a new brand new sheet with the sheet name and the column sum result.

I looked up on Microsoft website and I found the code to [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Loop through all worksheets but have no idea how to do de rest.

[/FONT]<strike>

</strike>[/SUP][/SUB][/U][/I][/B][LEFT][COLOR=#333333][FONT=monospace]Sub WorksheetLoop()


 ' Declare Current as a worksheet object variable.
 Dim Current As Worksheet


 ' Loop through all of the worksheets in the active workbook.
 For Each Current In Worksheets


    ' Insert your code here.
    ' So I guess that here will be the code select the column by looking for the first cell text (which has the header title), sum the numbers after the first cell until the last one with data on it 
[LEFT][COLOR=#333333][FONT=monospace]    ' And then put the results into a brand new sheet that includes the sheet name and the W column sum result[/FONT][/COLOR][/LEFT]
 Next


End Sub[/FONT][/COLOR][/LEFT][B][I][U][SUB][SUP]<strike>

</strike>

thank you for your help!​

[/FONT]

 

[mumps]

Try:

Sub SumCredit()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, desWS As Worksheet, fnd As Range
    Worksheets.Add(before:=Sheets(1)).Name = "Summary"
    Set desWS = Sheets("Summary")
    desWS.Range("A1:B1") = Array("Sheet Name", "Sum")
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set fnd = ws.Rows(1).Find("Credit", LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0) = ws.Name
                desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1, 0) = WorksheetFunction.Sum(ws.Range(ws.Cells(2, fnd.Column), ws.Cells(LastRow, fnd.Column)))
            End If
        End If
    Next ws
    desWS.Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

 

[OscarSuarez27]

Awesome! It works perfectly.
You are the best! Thank you!

 

[mumps]

You are very welcome.