Switch between two user selected workbooks with varying names

[Human_doing]

Hi all,

I am using this code to ask the user to open up two workbooks from which the macro will combine some data. The workbooks will always be in the same format but the names will differ.

Private Sub CommandButton1_Click()
'Ask user to select Excel file
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the report file")
NewFN2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the list file")
If NewFN = False Then
    ' They pressed Cancel
    MsgBox "No file selected."
    Exit Sub
Else
    Workbooks.Open Filename:=NewFN
End If
If NewFN2 = False Then
    ' They pressed Cancel
    MsgBox "No file selected."
    Exit Sub
Else
    Workbooks.Open Filename:=NewFN2
End If
 
NewFN.Sheets(1).Activate
 
End Sub

I was thinking this code assigns the first selection the file name NewFn and therefore I could use line of code NewFN.Sheets(1).Activate to navigate to that workbook but I receive error 'object required', could anyone please assist with allowing the code to assign 'names' to each workbook so I can reference them in other sections of the code?

Many thanks

 

[Andrew Poulsom]

Try:

Private Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook
'Ask user to select Excel file
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the report file")
NewFN2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the list file")
If NewFN = False Then
    ' They pressed Cancel
    MsgBox "No file selected."
    Exit Sub
Else
    Set wb1 = Workbooks.Open(Filename:=NewFN)
End If
If NewFN2 = False Then
    ' They pressed Cancel
    MsgBox "No file selected."
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=NewFN2)
End If
 
wb1.activate
wb1.Sheets(1).Activate
 
End Sub

 

[Human_doing]

Thanks so much for your help,

Cheers