Use Prompt to Assign variable to Workbook

[canyon]

Hello all,

I have a report I'm working on automating, right now I am working on some futureproofing. Right now I get two base files which do not have a standardized way of saving so calling it out in my current code requires me to change the workbook names every time I write the code.

Is there a way to use the input box or something else to assign the workbooks regardless of their name to a variable?

Workbooks("LRsalesOrders.xlsm").Worksheets("salesorders_1").Activate
Workbooks("WRSalesOrders.xlsm").Worksheets("salesorders_1").Activate

Set wbCopy = Workbooks("LRsalesOrders.xlsm").Worksheets("salesorders_1")
Set wbDest = Workbooks("WRSalesOrders.xlsm").Worksheets("salesorders_1")

This is currently what I'm running with as I start, but its a bit too static for what I have going on...

 

[6StringJazzer]

Here is one option. Note that I am using your wb variables for workbooks and created new ws names for worksheets. Also I got rid of your Activate statements which have no useful purpose here.

Sub YourSubStartsHere()

Dim wbCopy As Workbook, wbDest As Workbook
Dim wsCopy As Worksheet, wsDest As Worksheet

Set wbCopy = WorkbookSelectedByUser(Prompt:="Please select the copy file")
If wbCopy Is Nothing Then Exit Sub
Set wbDest = WorkbookSelectedByUser(Prompt:="Please select the destination file")
If wbDest Is Nothing Then Exit Sub

Set wsCopy = wbCopy.Worksheets("salesorders_1")
Set wsDest = wbDest.Worksheets("salesorders_1")

' Do the rest of your sub here

End Sub

' Ask user for file and open it
' Return the open workbook
' If user aborts, result is Nothing
Public Function WorkbookSelectedByUser(Optional Prompt As String = "Please select a file to open") As Workbook

   Dim UserFileName As Variant ' Variant because we are using to capture value
                               ' return by GetOpenFilename, which can return False
 
   UserFileName = Application.GetOpenFilename( _
                        Title:=Prompt, _
                        FileFilter:="Excel Files *.xls;*.xlsx (*.xls;*.xlsx),")

   If UserFileName = False Then ' must compare to False because it's a Variant
      MsgBox "No file specified."
   Else
      On Error GoTo OpenFailed
      Set WorkbookSelectedByUser = Workbooks.Open(FileName:=UserFileName)
   End If
 
   Exit Function
 
OpenFailed:

   MsgBox "Failed to open source file specified by user." & vbCrLf & _
          "Name: " & UserFileName & vbCrLf & _
          Err.number & ": " & Err.Description
        
   Exit Function

End Function