VLOOKUP using 2 workbooks that both change name regularly?

[gabbana]

Hi,

I run a simple VLOOKUP using 2 workbooks. This has traditionally been done manually, but now I need to automate the process every month:

I usually use something like,

=VLOOKUP(D1,'[PD1_Book1_15-09-22.xls]Live'!$A:$A,1,0) ..on the current workbook called NW 01-09, worksheet name "Master", cell F1. I then copy the entire function down the column. The end result being a lot of #N/A's which I filter out immediately.

The problem is both workbooks change their name regularly (eg.. "PD*_Book1_**-**-**.xls, in which case "Book1" is constant and NW **-**, in which case NW is constant)

So with "Live" sheet active on "PD*_Book1_*.xls" I've tried this, but can't get it to work at all.

Sub Lookup()
myFileName = ActiveWorkbook.Name
mySheetName = ActiveSheet.Name
myRangeName = Range("A:A")

Dim wb As Workbook
For Each wb In Workbooks
If wb.Name Like "NW*.*" Then
wb.Activate
Exit Sub
End If
Next

Range("F1").Formula = "=VLOOKUP(A1,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",1,0)"

End Sub

I am totally lost on this one, any help would be great. If I can't get it to work on 1 cell, I have no clue how to fill all of them!

The NW workbook will always be open as it's subject to a load of data cleaning via macro before we get to this point. I am happy for the PD*_Book1_**.xls to be opened now if it's easier. The data for lookup is on worksheet "Live"

I'm new to vba, and think i've bitten off more than I can chew. Thanks in advance.

[JOHN H. DAVIS]

Don't know if this helps in your situation. But when referencing workbooks, if you only have the two opened at the same time the first one opened can be reference as Workbooks(1) and the next as Workbooks(2). Also the workbook which houses the code can always be refered too as Thisworkbook.

[gabbana]

Does that mean

[gabbana]

Sorry John, my browser is playing up. Does that mean I could technically reference 3 workbooks at a time generically?

[JOHN H. DAVIS]

It means when you open the application the first workbook opened is Workbooks(1) in the workbooks collection and the next would be Workbooks(2) and so on.... If that helps too solve your referencing issue. Also the workbooks which houses the code can be referred too as Thisworkbook.

[gabbana]

Thank you, i'm nearly there I think, just getting a different error now for an unrelated bit. Have posted a different thread.

[JOHN H. DAVIS]

You're welcome. Wish you well.