For Each worksheet VBA

[SBF12345]

Greetings,

I am writing a short script to summarize account balances for each account included in a workbook. Each account has its own worksheet.

I want the macro to perform a simple set of calculations for each worksheet except for a few worksheets. To distinguish between worksheets I use the following lines of code.

For Each ws In ThisWorkbook.Worksheets

If (ws.Name <> "Balance Sheet") And (ws.Name <> "General Ledger") Then

The code is currently not recognizing the sheet "Balance Sheet" as something that should be avoided because it runs the script after "Then" on the sheet "Balance Sheet"

Also, it doesn't seem to want to go to the next sheet for some reason.

Here is the entire script:

Sub Balance_Sheet_Generator()'
' Macro5 Macro
' Macro recorded 6/17/2015 by RainShadow
'


Workbooks("General_Account_Ledger.xls").Activate


Dim ws As Worksheet


Worksheets("Balance Sheet").Activate


For Each ws In ThisWorkbook.Worksheets


If (ws.Name <> "Balance Sheet") And (ws.Name <> "General Ledger") Then


Range("F4").Select
Selection.Value = "Net Dr"


Range("F5").Select
Selection.Value = "=SUM(D5:D65536)"


Range("F6").Select
Selection.Value = "Net Cr"


Range("F7").Select
Selection.Value = "=SUM(E5:E65536)"


Range("F8").Select
Selection.Value = "NET ACCOUNT"


Range("F9").Select
Selection.Value = "=F5+F7"


End If


Next ws
'
End Sub

 

[mumps]

Try:

Sub Balance_Sheet_Generator() '
    Workbooks("General_Account_Ledger.xls").Activate
    Dim ws As Worksheet
    Worksheets("Balance Sheet").Activate
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Balance Sheet" And ws.Name <> "General Ledger" Then
            ws.Range("F4") = "Net Dr"
            ws.Range("F5") = "=SUM(D5:D65536)"
            ws.Range("F6") = "Net Cr"
            ws.Range("F7") = "=SUM(E5:E65536)"
            ws.Range("F8") = "NET ACCOUNT"
            ws.Range("F9") = "=F5+F7"
        End If
    Next ws
End Sub

Do you really need to sum 65536 rows in columns D and E?

 

[SBF12345]

HI, Sorry it took me so long to respond. Thanks for the response! In answer to your question no, I need to add all the numerical contents of those rows, not necessarily 65536 rows. I couldn't remember how to add an entire column and run speed of the macro isn't very important. Thanks!

 

[mumps]

My pleasure.