CODEWITHSUNDEEP
vbaexcel
stack mark
stack mark

Reputation: 101

Running VBA code across multiple sheets in a workbook

I use the below code that utilizes the sheet "Previous Week apps" for data collection and updates the count in the same sheet.

I have created a button and i'm trying to run this code through the button.

On execution i get the error as "Object required" in line 

[W5] = wf.CountIf(.Range("I:I"), "Trophy")

My code:

Sub Prevcount()

 Sheets("Previous Week apps")

 With ActiveWorkbook.Worksheets("Previous week apps")
  [W5] = wf.CountIf(.Range("I:I"), "Trophy")
End With

With ActiveWorkbook.Worksheets("Previous week apps")
[W7] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("E:E"), "COMPATIBLE")
End With

With ActiveWorkbook.Worksheets("Previous week apps")
[W9] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("F:F"), "COMPATIBLE")
End With

With ActiveWorkbook.Worksheets("Previous week apps")
[W11] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("Q:Q"), "UG")
End With

End With
End Sub

Upvotes: 0

Views: 291

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the short version (replaced ActiveWorkbook with ThisWorkbook , where you have your code):

Sub Prevcount()

With ThisWorkbook.Worksheets("Previous week apps")
  .Range("W5").Value = WorksheetFunction.CountIf(.Range("I:I"), "Trophy")
  .Range("W7").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("E:E"), "COMPATIBLE")
  .Range("W9").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("F:F"), "COMPATIBLE")
  .Range("W11").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("Q:Q"), "UG")
End With

End Sub

Upvotes: 1

Related Questions