CODEWITHSUNDEEP
vbaexcel
Apodiectic
Apodiectic

Reputation: 1

Looping through worksheets in active workbook not working

I am working with multiple worksheets (dynamic) and want to do some simple calculations on each sheet (VBA Beginner). I can do the macro on each sheet individually but the loop will not work. No error message, but only executes on one sheet. Code is below. Any help is appreciated. 

Sub Counts()

Dim Last As Integer
Dim Have As Long
Dim Miss As Long
Dim Total As Long
Dim Value As Currency
Dim Cost As Currency
Dim TVal As Currency
Dim ws As Worksheet



For Each ws In ActiveWorkbook.Worksheets

Last = Range("B1").CurrentRegion.Rows.Count

Miss = Application.WorksheetFunction.CountBlank(Range("A2:A" & Last))
Total = Last - 1
Have = Total - Miss

Value = Application.WorksheetFunction.SumIf(Range("A2:A" & Last), "X",Range   ("G2:G" & Last))
TVal = Application.WorksheetFunction.Sum(Range("G2:G" & Last))
Cost = TVal - Value

Range("J2").Value = "Have"
Range("J3").Value = "Missed"
Range("J4").Value = "Total Cards"
Range("J6").Value = "Value"
Range("J7").Value = "Cost to Complete"
Range("J8").Value = "Set Value"

Range("k2").Value = Have
Range("k3").Value = Miss
Range("k4").Value = Total
Range("k6").Value = Value
Range("k7").Value = Cost
Range("k8").Value = TVal

Next ws

End Sub

Upvotes: 0

Views: 98

Answers (1)

YowE3K
YowE3K

Reputation: 23994

By default Range refers to the active sheet, and looping through the sheets does not mean each of those sheets is automatically activated. You should always try to specify exactly which sheet you are referring to. The code below uses ws.Range wherever you used Range. (And uses a With ws block so that ws.Range can be shortcutted to just .Range.)

Sub Counts()

Dim Last As Integer
Dim Have As Long
Dim Miss As Long
Dim Total As Long
Dim Value As Currency
Dim Cost As Currency
Dim TVal As Currency
Dim ws As Worksheet



For Each ws In ActiveWorkbook.Worksheets

    With ws
        'I modified the next line to not use "CurrentRegion" as I wasn't
        'sure whether "CurrentRegion" would be meaningful without first
        'selecting a cell.
        Last = .Range("B" & .Rows.Count).End(xlUp).Row

        Miss = Application.WorksheetFunction.CountBlank(.Range("A2:A" & Last))
        Total = Last - 1
        Have = Total - Miss

        Value = Application.WorksheetFunction.SumIf(.Range("A2:A" & Last), "X",.Range("G2:G" & Last))
        TVal = Application.WorksheetFunction.Sum(.Range("G2:G" & Last))
        Cost = TVal - Value

        .Range("J2").Value = "Have"
        .Range("J3").Value = "Missed"
        .Range("J4").Value = "Total Cards"
        .Range("J6").Value = "Value"
        .Range("J7").Value = "Cost to Complete"
        .Range("J8").Value = "Set Value"

        .Range("k2").Value = Have
        .Range("k3").Value = Miss
        .Range("k4").Value = Total
        .Range("k6").Value = Value
        .Range("k7").Value = Cost
        .Range("k8").Value = TVal

    End With    
Next ws

End Sub

Upvotes: 2

Related Questions