Macro to add sum to all worksheets in a workbook except for a certain worksheets

[feni1388]

Hello

I have a macro that split a master data into several worksheets.
The number of worksheets changes every months.
I need to add the sum total of sales that will be in column G. The number of rows will also changes, sometime only one row.
There are 3 sheets that have different format and there's no need to add sum to these 3 sheets.

So I need a macro that will loop through all the sheets within the workbook, except for those 3, and add the sum in the end of column G.

Sub Addingsumtoeachsheet()

Dim sh As Worksheet
Dim I As Integer
Dim Ws_Number As Integer
Dim SumRg As Range

For Each sh In ActiveWorkbook.Worksheets
Select Case sh.Name
Case Is = "Unique", "Sales Report", "Sales details"

Case Else
With sh
Set SumRg = ActiveSheet.Range("G2", Range("G2").End(xlDown).End(xlToRight))
Range("G2").End(xlDown).Offset(1, 0) = WorksheetFunction.Sum(SumRg)
Columns("G:G").Select
Selection.Style = "Comma [0]"
End With
End Select
Next sh

End Sub

I tried the above macro code and it seems that excluding those 3 worksheets works, but not with adding the sum to each worksheets.

can someone please help?

Thank you in advance.

 

[Alex Blakenburg]

Do you want to finish up with the Sum formula in the spreadsheets or just the result of the sum ?
You are currently using the worksheet function to insert just the value and not the formula. This seems an unlikely intention. In the below I have provided both options.

Sub Addingsumtoeachsheet_mod()

    Dim sh As Worksheet
    Dim I As Integer
    Dim Ws_Number As Integer
    Dim SumRg As Range
    
    For Each sh In ActiveWorkbook.Worksheets
        Select Case sh.Name
        Case Is = "Unique", "Sales Report", "Sales details"
        
        Case Else
            With sh
                Set SumRg = .Range("G2", .Range("G" & Rows.Count).End(xlUp))
                ' This puts the Sum formula into the spreadsheet
                .Range("G" & Rows.Count).End(xlUp).Offset(1, 0) = "=Sum(" & SumRg.Address & ")"
                ' Comment out the line above and Uncomment the next line if you just want the result of the Sum
                '.Range("G" & Rows.Count).End(xlUp).Offset(1, 0) = WorksheetFunction.Sum(SumRg)
                .Columns("G:G").Style = "Comma [0]"
            End With
        End Select
    Next sh

End Sub

 

[feni1388]

Actually I want to finish up with the Sum formula.
Thank you. It works as I expected.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.