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.

 

[RockandGrohl]

Always nice when that happens.

Glad to help.

Yeah that was exactly what I was after. Knew it could be done because that's the way Excel works when you 'set' a variable, it will lock in to the sheet. I just had absolutely no idea how to name it. Thanks again.