How do I make a VBA SUMIFS reference the right sheet?

[RockandGrohl]

Hello hello hello,

I'm trying to replace a formula within a Macro to optimise my code a bit.


Previously, this was written and references a workbook called Capacity that was already opened previously in the code:

Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS(Capacity.csv!C3,Capacity.csv!C1,RC1)"

So what I've done in the name of optimisation is I have made a copy of Capacity within the workbook.


The trouble is that this formula is placed in another external workbook that's opened called "ads"


So ads is opened, and this formula is placed in cell Z:

=SUMIFS(C:C,A:A,A2)

It will sum cells C:C of Capacity based on a criteria range of A:A in Capacity, then the criteria is in cell A2 of ads


I need to replace this to show that C:C and A:A are in a tab called "CAP" in the original workbook.

What I've done is re-record the macro with the sumifs now looking in the right place, however it gives me this:

"=SUMIFS('[Reader Offer Portal - Dev.xlsm]CAP'!C3,'[Reader Offer Portal - Dev.xlsm]CAP'!C1,R[-1]C[-25])"

The trouble here is that this workbook name can change many times, it could be called "Reader Offer Portal - JG" or "Reader Offer Portal - K1" etc

I'd rather not use '[Reader Offer Portal*]' and I was wondering whether I can just get it to look in CAP instead. If I write the following:

"=SUMIFS('CAP'!C3,'CAP'!C1,R[-1]C[-25])"

It just looks to open a workbook called 'CAP'

Thanks.

 

[steve the fish]

When you open the workbook

that was already opened previously in the code

how do you reference it if its name keeps changing?

 

[RockandGrohl]

When you open the workbook



how do you reference it if its name keeps changing?

Ok so going back to the beginning would be beneficial for all.

There was a workbook called a Reader Offer Portal, this had a few inconsequential tabs but one of them was called Adselect which was set to a.

Before, when you opened a userform it would look at 5 different external workbooks and various tabs in them to get data, this was repeated 600 times each week which you can appreciate is a lot of time waiting for the same books to load, values to be found, workbook to be closed.

I've built a process where this Portal has these external sheets built in to it, on separate tabs, and these are refreshed weekly. Hopefully this cuts down the loading time significantly.


To the current point, when loading the userform it will load up Capacity and Ads. It then sumifs Capacity col. C against criteria range capacity col A and then looks at ads cell A2 and so on.

I now need this to look at Capacity Col C and Capacity Col A, but instead of looking in Capacity.csv, it just needs to look at tab "CAP" (which I've set to 'cap') for the data to sum.


The trouble is that this calculation is done in Column Z of ads (which is still an external workbook as it contains live data that cannot be replicated)


And because this is done in ads, it means that it will always want to open up the formula to look like this:

"=SUMIFS('[Reader Offer Portal - Dev.xlsm]CAP'!C3,'[Reader Offer Portal - Dev.xlsm]CAP'!C1,R[-1]C[-25])"

 

[steve the fish]

You didnt really answer how you reference this workbook earlier in the code if its name keeps changing unless i missed it.

 

[RockandGrohl]

You didnt really answer how you reference this workbook earlier in the code if its name keeps changing unless i missed it.

The portal workbook tab will always be 'a'

So it'll be like a.activate

Unless I'm missing something I shouldn't need to reference this workbook within the formula. The information I need to sumif in capacity.csv now sits within a tab in the portal workbook called "cap"

If I could replace the original code's Capacity.csv

[COLOR=#333333]Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS(Capacity.csv!C3,Capacity.csv!C1,RC1)"[/COLOR]

With "cap" somehow, that would be best, but I can't workout the syntax to do this.

All the information I now need related to capacity is now on "cap" so I just want to point the formula to that, however it wants to add all the other crap like '[Reader Offer Portal - Dev]!cap' to it. I want to know if I can replace that with a workbook name I've set myself.

 

[RockandGrohl]

Having just tried this, it still wants me to open a workbook:

Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS('" & cap.Name & "'!C3,'" & cap.Name & "'!C1,RC1)"

 

[RoryA]

If a is a variable representing the data workbook, you'd need:

Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS('[" & a.Name & "]CAP'!C3,'[" & a.Name & "]CAP'!C1,RC1)"

 

[RockandGrohl]

If a is a variable representing the data workbook, you'd need:

Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS('[" & a.Name & "]CAP'!C3,'[" & a.Name & "]CAP'!C1,RC1)"

Hi Rory,

This looks promising but it still wants me to open "Adselect" (so at least it's figured out that 'a' belongs to 'adselect'

Cheers

 

[steve the fish]

Essentially what rory is saying is that this is not correct:

[COLOR=#333333]Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS(Capacity.csv!C3,Capacity.csv!C1,RC1)"[/COLOR]

This will be looking for a sheet named 'Capacity.csv' within the activeworkbook not a workbook named 'Capacity.csv' which would then need an appropriate sheet also.

 

[RockandGrohl]

Essentially what rory is saying is that this is not correct:

[COLOR=#333333]Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS(Capacity.csv!C3,Capacity.csv!C1,RC1)"[/COLOR]

This will be looking for a sheet named 'Capacity.csv' within the activeworkbook not a workbook named 'Capacity.csv' which would then need an appropriate sheet also.

Sure, so the way it was, where Adselect only had 4 tabs, 3 are not important, the one we need to think about is 'a'

What I've done is made a clone of that portal, and introduced a series of additional tabs which hold the data which were on workbooks that were being repeatedly opened, accessed then closed.

So now Capacity.csv has become 'cap'

However whenever I try to reference 'cap' instead of 'capacity.csv' in this sumif it still wants to look for an external workbook to open.


Just to note, you're saying above that it is not correct, but I can confirm it definitely works. This is the original, unaltered code in complete form:

Set ads = Worksheets("Advert Data " & wbyr)


'Locate already-placed tours (if any)


temp.Activate
Range("B1").Activate
ads.Activate


Application.DisplayAlerts = False
Set remcap = Workbooks.Open("\\CHW-INTRANET\output\salesopt\Capacity.csv", False, True)
Application.DisplayAlerts = True


ads.Activate
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("Z3:Z" & lastrow).FormulaR1C1 = "=SUMIFS(Capacity.csv!C3,Capacity.csv!C1,RC1)"
Range("Z3:Z" & lastrow).Copy
Range("Z3").PasteSpecial xlPasteValues


Application.DisplayAlerts = False
remcap.Close
Application.DisplayAlerts = True