Can this be done in a Loop?

[MyrenG1]

Hi,

I've been through this in a painfully repetitive manual way. I was wondering if there is a way of placing the below in a Loop and only changing a few things inside the "COUNTIFS" statement and wsSummary.Range.

I haven't done any loops before, I am still fairly new when it comes to VBA scripting. I just know how to adapt coding to help me with what I try to achieve.

Any help with the below would be appreciated. Thank you for your time in Advance.

The elements inside the COUNTIFS that change are:-

- "Sheet1"
- "$A$23"

Also:-

- "With wsSummary.Range("B1")"

It will need to loop approx 6 times to complete population of the Table. If this can work, I should be able to adapt it to my other tables and forumla I have.

' Identify Sheet as worksheet'

Dim wsSummary As Worksheet

' Identify Formula as a String'

Dim sFormula As String

' Calculate Yes or No Totals'

' Total Yes for Sheet1'

    Set wsSummary = Sheets("Summary")
    sFormula = "COUNTIFS(Sheet1!$D$D, $A$2, Sheet1!$E:$E, B1)+COUNTIFS(Sheet2!$D$D, $A$2, Sheet2!$E:$E, B1)+COUNTIFS(Sheet3!$D$D, $A$2, Sheet3!$E:$E, B1)+COUNTIFS(Sheet4!$D$D, $A$2, Sheet4!$E:$E, B1)+COUNTIFS(Sheet5!$D$, $A$2, Sheet5!$E:$E, B1)+COUNTIFS(Sheet6!$D$, $A$2, Sheet6!$E:$E, B1)"
    
With wsSummary.Range("B2")
    .Formula = sFormula
    wsSummary.Calculate
    .Value2 = .Value2
End With

Example Table below of the results I am looking for:-

	A	B	C	D	E	F	G
1		Sheet1	Sheet2	Sheet3	Sheet4	Sheet5	Sheet6
2	Yes	1	2	3	4	5	6
3	No	6	5	4	3	2	1

 

[fadee2]

yes i did and the result was what you posted in your first post. are you getting any errors with the code? ehat is the result??

 

[MyrenG1]

I have an extra column for "Total" in row H which is where it Stops and the Code Error's. I assume because it can't find a sheet named "Total".

 

[fadee2]

this is most probably the case. cant say for sure since i havent seen your worksheet. what is the error you are recieveing? i would suggest sharing part of yout worksheet on free sites dropbox or google drive, to have alook at.