For Each worksheet VBA

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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.

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:

Code:
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,111
Try:
Code:
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?
 
Last edited:

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top