Question related to AutomateSum

%3CLINGO-SUB%20id%3D%22lingo-sub-2256436%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20AutomateSum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256436%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20have%20written%20VBA%20code%20which%20is%20showing%20the%20AutomateSum.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E1st%20question%3C%2FSTRONG%3E%20---%20I%20want%20to%20show%26nbsp%3B%3CSTRONG%3ETOTAL%20SALES%26nbsp%3B%3C%2FSTRONG%3Ewhere%20total%20calculation%20has%20come%20in%20all%20sheet.%20Like%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2427).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270462i9625EDE88A141B5E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2427).png%22%20alt%3D%22Screenshot%20(2427).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E2nd%20question%3C%2FSTRONG%3E%20---%20I%20hide%20the%20NORTH%20RECORDS%20sheet%2C%20so%20when%20i%20run%20this%20code.%20So%20it%20gives%20error%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2428).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270464iA032EE745A20DC25%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2428).png%22%20alt%3D%22Screenshot%20(2428).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20click%20in%20DEBUG%20button%2C%20then%20it%20highlighted%20this%20line%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2429).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270465iBE89F2065EA56276%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2429).png%22%20alt%3D%22Screenshot%20(2429).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2256436%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Regular Contributor

Hello Everyone,

I have written VBA code which is showing the AutomateSum. 

 

1st question --- I want to show TOTAL SALES where total calculation has come in all sheet. Like - 

Screenshot (2427).png

 

2nd question --- I hide the NORTH RECORDS sheet, so when i run this code. So it gives error - 

Screenshot (2428).png

 

I click in DEBUG button, then it highlighted this line - 

Screenshot (2429).png

 

Please help.

 

Here is the attached file.

2 Replies

@Zan_Hanifee 

If you want to skip hidden sheets:

Public Sub AutomateSum()
    Dim wsh As Worksheet
    Dim LastRow As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        If wsh.Visible = xlSheetVisible Then
            LastRow = wsh.Range("F1").End(xlDown).Row
            wsh.Range("A" & LastRow + 1).Value = "TOTAL SALES"
            wsh.Range("F" & LastRow + 1).Formula = "=SUM(F2:F" & LastRow & ")"
        End If
    Next wsh
    Application.ScreenUpdating = True
End Sub

If you want to include hidden sheets:

Public Sub AutomateSum()
    Dim wsh As Worksheet
    Dim LastRow As Long
    Application.ScreenUpdating = False
    For Each wsh In Worksheets
        LastRow = wsh.Range("F1").End(xlDown).Row
        wsh.Range("A" & LastRow + 1).Value = "TOTAL SALES"
        wsh.Range("F" & LastRow + 1).Formula = "=SUM(F2:F" & LastRow & ")"
    Next wsh
    Application.ScreenUpdating = True
End Sub

This code doesn't select cells, so it works for hidden sheets as well as for visible sheets.

It worked! Thank you so much sir