Macros with daily Vlookup

[bimo]

Hello

I use a refreshable Excel Report on a daily basis, and I would like a VBA code that would save me time by executing some repetitive actions,

The refreshable report is saved Daily as “DDMMYY AS”


In Work book, say “DDMMYY AS” which is today`s version, I would like to :


1- insert a Column in Column J

2 - in I7 look up adjacent H7 and return value of I7 from work book saved previously as " DD-1MMYY AS " sheet " ALL orders"

3 - in J7 look up Cell H7 and return value of J7 in work book saved previously as " DD-1MMYY AS " sheet " ALL orders"

4-Drag down I7 and H7 to copy formula into the below cells up to the empty cell in Column H


Attached the 3 report reports

Refreshable report before modification,
saved version for the day before 050613 AS
The one with final outcome 060613 AS

Thank you for looking into it ..any more info needed, please ask…

[p24leclerc]

Would like to help but not sure what you want to do.
First, I saw columns (O, P, Q, R, S, T,U ,W) in Refreshable report not present in ddmmyy AS workbook. Were they deleted? Which ones have to be deleted?
Secondly, I assmue you are always working on sheet All Orders. Right?
Now, the column you add is called Reasons. Right? And you want to get values from previous day file for column I and J based on column H values. Am I still right?
Why would you want a formula to be used in column I and J when the macro can do this in a breeze? And it makes your file a lot smaller and faster if you happen to have lots of data.
Lastly, do you have a report every day? Even in weekends or holidays? I doubt it. Then the macro will have to look for the previous report not knowing exactly its name.
I might be able to help but I need some answers first.
Regards

[bimo]

Would like to help but not sure what you want to do.
First, I saw columns (O, P, Q, R, S, T,U ,W) in Refreshable report not present in ddmmyy AS workbook. Were they deleted? Which ones have to be deleted?

They should be present on all workbook, sorry I deleted them by mistake,

[/QUOTE]
Secondly, I assmue you are always working on sheet All Orders. Right? [/QUOTE]
That is Wright

Now, the column you add is called Reasons. Right? And you want to get values from previous day file for column I and J based on column H values. Am I still right? [/QUOTE]
Yes !


Why would you want a formula to be used in column I and J when the macro can do this in a breeze? And it makes your file a lot smaller and faster if you happen to have lots of data.[/QUOTE]
The Macros that is Built in the file "Refresh" basically brings in new rows and keep some of the old ones, and I make notes against each row on column I & J, I would like the added macros can retreive my old comments and rating from previous report.

Lastly, do you have a report every day? Even in weekends or holidays? I doubt it. Then the macro will have to look for the previous report not knowing exactly its name.[/QUOTE]

You wright it is only working days..


Hope this clarify the situation, and any question, please shoot..

Thanks for taking the time to look into it

[p24leclerc]

See attached file. I created a button to run the new macro that will create the new daily report and update data from previously saved report.
Daily report and previous day reports must be in the same folder as the Refreshable report workbook.
After update, it closes the previous day report.

Be advise that daily report file name will not have leading 0 for day and month. So your file should be renamed as 5613 AS.xls instead of 050613 AS.xls
Also, macro is looking for file with .xls extension. If you want to use the .xlsx extension, you'll have to modify the macro.

Hope it's what you were looking for

[bimo]

IT WORKED LIKE MAGIC! top man ..Thank you!