Copy in formula linked to variable other workbook

[rpinxt]

So this is the issue.
For my workbook I need data from a workbook from other month.
So I use VBA to open the other months workbook and then use a formula to get the data I need.
This works fine for the month in which i recorded the macro.
But of course I now need to make it variable.

I tried with this:

Please Login or Register  to view this content.

But this still gives me a popup and I have to navigate myself to the workbook.
So this is not working.

This is the original formula not made variable:

Please Login or Register  to view this content.

Anybody know how to get this to work?

[ragulduy]

You need to separate the variable and strings of the formula, so instead of "=sumifs[bbwb]asodfn" for example, you need "=sumifs[" & bbwb & "]asodfn"

For your code:

Please Login or Register  to view this content.

[rpinxt]

Hmm ok of course because it is a variable.
However this one is returning me the 'object doesn't support this property or method' error....

[rpinxt]

I don't think this ".Activatesheet.Range("E10")" bit of code is correct?
Did you mean it to be ActiveSheet?
But that also does not work unfortunately

[ragulduy]

Please Login or Register  to view this content.

?

[rpinxt]

Yes thats what I also tried.
But still an error. It is in the formula.
It highlights the formula as having an error.

[ragulduy]

ah right yeah, you've got some A1-style references in there, C7 should be RC7 or R[-3]C[-2], depending what you mean by it.

[rpinxt]

Hmm not sure what you mean with that, the macro has been recorded so I would think the sumifs formula would be ok like that.
But you probably mean because the formula has been made variabel also the formula format changes?

[ragulduy]

Yeah I was talking rubbish.

Try changing BBWB to BBWB.name

Please Login or Register  to view this content.

[rpinxt]

You're the King
That nailed it, thanks!