Hi all,
I am trying to use a countifs function to get my data on multiple sheets. The code is as follows :
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.
I am trying to use a countifs function to get my data on multiple sheets. The code is as follows :
Code:
[/COLOR]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.