Changing file references in a macro

[Guest]

Hi,

this is a bit tricky, here goes...

I am working on workbook 'Sales 04 12m' and on worsheet 'alpha 12m 04'.
I put a vlookup function that looks up data from a table array in another
workbook for the previous month, which has the same name format "Sales YY
MMm" and from a similar worksheet "alpha 11m 04'.

This macro I use in three different sheets in the workbook I can's go
changing the names each time in the macro. I need a way to specify the
workbook/worksheet from upfront. or even better beacuse it is always one
month back, the macro could figure out the name by itself , could it??

I tried InputBox inside the vllokup function but i get an error, mayby I am
not supposed to use it that way.

Any help appreciated.

Thank you

 

[Markus Scheible]

Hi unknown,

try:

Format(Month(Date), "MM")
Format(Year(Date), "YY")

you can use this as reference for your workbook to open
like:

workbooks.open filemane := "c:\some path here\"& Format
(Month(Date), "MM") & ".xls"

Best

Markus

 

[Guest]

Marcus,

I am not sure what to do with the code you send me. Let me show you the code
I have.

Range("A1").Select
Columns("D").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = _

"=IF(RC[-2]=0,"""",VLOOKUP(R[158]C[-2],'[Sales_04-11m.xls]Sale11m''04
(ii)'!R2C2:R65000C4,3,FALSE))"
ActiveWindow.SmallScroll Down:=-3

I want to be able to change the 04-11 part and the 11m'04 part.

Thank you,

bay