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.

 

[RoryA]

It sounds to me like the sheet name is not actually CAP. Are you sure there aren't leading or trailing spaces for example?

 

[RockandGrohl]

It sounds to me like the sheet name is not actually CAP. Are you sure there aren't leading or trailing spaces for example?

Yep, positive. Here's the code now:

Set cap = Worksheets("CAP")

ads.Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("Z3:Z" & Lastrow).FormulaR1C1 = "=SUMIFS('[" & a.Name & "]CAP'!C3,'[" & a.Name & "]CAP'!C1,RC1)"
Range("Z3:Z" & Lastrow).Copy
Range("Z3").PasteSpecial xlPasteValues

 

[RoryA]

If that pops up a dialog to open the workbook, then the sheet name is not correct, or it's not in the workbook that a refers to.

 

[RockandGrohl]

If that pops up a dialog to open the workbook, then the sheet name is not correct, or it's not in the workbook that a refers to.

I think what's happening is the workbook is named "Reader Offer Portal - Dev"

The tab which is the master sheet that is referred to is 'a' (Adselect)

The tab which contains the Capacity data is named "CAP" and set to 'cap'


However, when the SUMIFS code below runs:

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

It wants to open a workbook named 'Adselect'


Which doesn't exist.

I don't understand why, if I have a worksheet set as 'CAP', why I can't reference this in a VBA formula, because it shouldn't matter which workbook it sits on purely because I tell it what workbook it sits on, earlier on in the code, when I declare that Set cap = Worksheets("CAP")


This means that whenever I say "cap.activate" for instance, no matter which external workbook or worksheet the VBA is looking at, it will know to go back to the Reader Offer Portal and activate the cap tab.

My question boils down to - why can I not just reference cap in the SUMIF formula.


The issue is that these portals are duplicated, renamed and we often end up with 4 separate portals so 4 separate people can work out of them through the week. So if I am just referencing one cap, for instance, it may update capacity data from a Reader Offer Portal which has old capacity data.


I really need this sumif to point towards cap, which will be the cap that was set on whatever Reader Offer Portal is open.

 

[RoryA]

Because you are building a formula string, which requires the workbook and worksheet names for an external reference.

Are Adselect and CAP in the same workbook?

 

[RockandGrohl]

They are mate, and that workbook is the 'Reader Offer Portal - Dev'

Here are the initial declarations:

Dim wbk As Workbook, a As Worksheet, ad As Workbook, ads As Worksheet, adopen As Boolean, temp As Worksheet, sp As Workbook, sps As Worksheet, PPU1 As String, PPU2 As String, PPU3 As String, PPU4 As String, EPap As String, wcs As String, ttype As String, rt As Worksheet, rs As Worksheet, acol As Long, stationfail As Boolean, durrest As Boolean, durlim As String, depch As Boolean
Set a = Worksheets("AdSelect")
Set rt = Worksheets("Rail Tours")
Set rs = Worksheets("Rail Supplement")

Would it be an idea to say, instead of a.name, put in "wbk"

I'm trying to find where in the code my predecessor said "THIS workbook is 'wbk' " or whatever he did.

 

[RoryA]

You can use a.Parent.Name then to get the workbook name.

 

[RockandGrohl]

You can use a.Parent.Name then to get the workbook name.

Forgive my ignorance, please, but does this go inside the SUMIFS or is it placed above so I can tell what the name of the parent worksheet is?

And secondly, once I know the name of the parent (assuming it is wbk) would it be [" & wbk.name & "]!CAP?

Thanks.

 

[RockandGrohl]

You can use a.Parent.Name then to get the workbook name.

Oh my bloody god I bunged it in there on a whim and it only went and bloody worked!!!

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

Love you pal.

I probably could've gotten an answer much faster if I wasn't so terrible at explaining my predicament.

Thanks v v much.

 

[RoryA]

Oh my bloody god I bunged it in there on a whim and it only went and bloody worked!!!

Always nice when that happens.

Glad to help.