How to use VBA to VLOOKUP across workbooks, sheet by sheet

0
votes

my apologies in advance - I am relatively novice with VBA

I have a report generated daily, with several sheets (Let's say Sheet1, Sheet2, Sheet3). These sheets have tables where I track notes for each order line, and I would like to vlookup the notes from day to day.

The issue I am facing: I need to be able to repeat this for Sheet1, Sheet2, and Sheet3, pulling from the previous day's respective Sheet1, 2, 3, and repeat this day by day.

Is there a way for the vlookup to recognize my active sheet name, e.g. Sheet3, and look back at the prior day's workbook and pull from that Sheet3?

Is this something that can be done?

Thank you for any help

1
vbaexcelmacrosvlookup
There are many ways to accomplish this. Unfortunately you are asking several questions in one post (whether you know it or not). Stack Overflow is for more targeted questions. I recommend that you attempt to record a macro that accomplishes what you are trying to do. – n8.
Hi n8 thank you for the reply - I will edit my original post to reflect the specific problem I am trying to address – tyler p
@n8. Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("D2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP([@Column1],'[PLS DMD 022416.xlsm]Sheet3'!C3:C4,2,FALSE)" Range("D3").Select This is what I am presently working with but this would only apply to pulling from Sheet3 onto the new 022516 Sheet3. This would also not allow me to repeat this the next day from (PLS DMD 022616) as it would not reference 022516, but still 022416 – tyler p
Are you going cell-by-cell? – n8.
@n8. I am trying to go sheet by sheet, the vlookup is supposed to populate downward for each line – tyler p

1 Answers

0
votes

Before you get where you are trying to go you need a consistent starting point. I would create a new workbook and hold your logic there. You don't need your macro to be in the workbook that your data is.

Like this: enter image description here

Using named ranges is best in case you want to change the look of your control workbook, and you can create formulas for the file names so that they default to your current day and prior workday.

The code would launch both workbooks. Hopefully this is a valid starting point for you and gives you an idea how to proceed.

Sub mergeThese()

    path_New = Range("path_New")
    path_Old = Range("path_Old")
    file_New = Range("file_New")
    file_Old = Range("file_Old")

    Workbooks.Open path_Old & file_Old
    Workbooks.Open path_New & file_New

End Sub