Need to Create a Popup Macro that will allow user to select destination file

[YukonSoDank]

The code below works well by opening a destination file template and pasting certain rows from the source file to the destination file. The problem is I will eventually need to share these files and allow others to use them. You will see that the portion that executes the opening of the destination file is very specific (highlighted in red). I would like to create a popup chooser in place of that portion so that a new user can choose the template based on where they have saved it on their machine. I've never done this so any help is greatly appreciated.

Please Login or Register  to view this content.

Sub CopyDataAndSave()
Application.ScreenUpdating = False
Dim desWB As Workbook, desWS As Worksheet, srcWS1 As Worksheet, srcWS2 As Worksheet
Set srcWS1 = ThisWorkbook.Sheets("Detailed Outputs")
Set srcWS2 = ThisWorkbook.Sheets("User Inputs")
Set desWB = Workbooks.Open(Environ("userprofile") & "\Desktop\Upwork\Model_Template.xlsx")
Set desWS = Sheets("Input")
With srcWS1
.Range("B8:M8").Copy
desWS.Range("Q4").PasteSpecial xlPasteValues
.Range("B45:M45 ").Copy
desWS.Range("Q8").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & srcWS2.Range("C14").Value & ".xlsx", FileFormat:=51
Application.ScreenUpdating = True
End Sub

Please Login or Register  to view this content.

[MarvinP]

Hi YukkonSoDank,

There is a built-in dialog that might do what you want. Try "Application.FindFile" and it will open a file selection dialog. If you set the desWB to the file the user finds and clicks on, it might do exactly what you need.

Read some more at: http://www.44342.com/ms-excel-f84-t51875-p1.htm