Changing Input Files on a Recorded Macro

[bdev1982]

Hi all,

Wondering if I could get some assistance on this macro I have recorded. Basically, I'm cutting columns from one spreadsheets and pasting them into a new spreadsheet, then apply some basic vlookups referencing a 2nd file, and some further calculations (multiplications). The macro works great but I'm now stuck because the macro references the input file names from the recording. The source documents are downloaded files and the file name will always be different, so I need to be able to direct the macro to change the source file names.

Is there something I can add to my Macro, where I type in a Filename 1 & Filename 2 value and the macro is updated accordingly with the press of a button?

Thanks!

 

[Joe4]

Welcome to the Board!

What I would recommend doing is having the opening of these files as part of the Macro. Then, you can "capture" the workbook and reference it dynamically in your code.

Here is a little example which shows you how to capture the Macro workbook in a workbook variable, how to open a new file and set that to a workbook variable, and how to bounce back and forth between workbooks.

Sub MyTest()

    Dim wb1 As Workbook
    Dim newFile As Variant
    Dim wb2 As Workbook
    
'   Set macro workbook to wb1
    Set wb1 = ActiveWorkbook
    
'   Browse to second file, open it, and set to wb2
    Dim fNameAndPath As Variant
    newFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS*), *.XLS*", Title:="Select File To Be Opened")
    If newFile <> False Then
        Workbooks.Open Filename:=newFile
        Set wb2 = ActiveWorkbook
    End If
    
'   Return names of files
    MsgBox "wb1 name: " & wb1.Name & vbCrLf & "wb2 name: " & wb2.Name
    
'   Go back to first file
    wb1.Activate
    
End Sub

 

[bdev1982]

Awesome! Thanks, I was able to configure the macro to open a third file and have been able to cut and paste the data from the two source files to the new document. I am running into an issue that the VLOOKUP formula is not recognizing wb3, and providing #N/A returns.

Here is the Vlookup formula I am presently using

ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-4], 'wb3!'C1:C5,3, FALSE)"

When I used the following formula, I got positive results, so I believe the table array portion of my formula is wrong.
"=VLOOKUP(RC[-4], '[Book1.csv]Sheet1'!C1:C5,3, FALSE)"

Thanks!

 

[Joe4]

You cannot use "wb1" in the VLOOKUP formula. It is a VBA variable, not a literal workbook name you can use in a VLOOKUP function.

To get it to do what you want, you will need to return Name property of that variable (and you still need to include the square brackets and Sheet name, which is not part of the workbook name), i.e.

[COLOR=#333333]"=VLOOKUP(RC[-4], '[" & wb1.Name & "]Sheet1'!C1:C5,3, FALSE)"[/COLOR]