Activating multiple sheets with countifs function

[xboon_95]

Hi all,

I am trying to use a countifs function to get my data on multiple sheets. The code is as follows :

ActiveSheet.Range("S1").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""P"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S2").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""I"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S3").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""O"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S4").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""C"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S5").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""L"",ActiveSheet!I:I,""1"")")

As the sheet name varies, I'm not able to use the constant name Sheet1!. I tried changing (Sheet1!D,""P"",Sheet1!I:I,""1"") to (ActiveSheet!D,""P"",ActiveSheet!I:I,""1"") but it doesn't seem to work. Any idea how to do it? Any help will be appreciated. Thank you.

[Bob Phillips]

Maybe this will work

With ActiveSheet

.Range("S1").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""P"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S2").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""I"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S3").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""O"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S4").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""C"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S5").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""L"",'" & .Name & "'!D:D,""!I:I,""1"")") 
End With

[ranman256]

You dont need the word ACTIVESHEET. Excel assumes your formula is for the activesheet so you dont need to put it in the formula. Just use the range.

[xboon_95]

Maybe this will work

With ActiveSheet

.Range("S1").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""P"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S2").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""I"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S3").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""O"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S4").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""C"",'" & .Name & "'!D:D,""!I:I,""1"")") 
.Range("S5").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""L"",'" & .Name & "'!D:D,""!I:I,""1"")") 
End With

Thank you very much (:

[Bob Phillips]

You dont need the word ACTIVESHEET. Excel assumes your formula is for the activesheet so you dont need to put it in the formula. Just use the range.

It is true that you don't, but it is not a good practice to omit a sheet reference, including it shows that you have explicitly decided you want it to work on the activesheet, and it is much easier to change code that is explicit if things change.