Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file references in a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file references in a macro
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing file references in a macro
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: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 "Markus Scheible" wrote: 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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing a macro each time the file name changes | Excel Discussion (Misc queries) | |||
Unable to run excel macro after changing file name | New Users to Excel | |||
How do I create a macro, that references another excel file ??? | Excel Worksheet Functions | |||
Using macro to save xls as txt without changing originial xls file | Excel Programming | |||
Using Macro to Save xls to txt without changing file format | Excel Programming |