I am trying to make a copy of a workbook that I have, based on list of IDs. I have got this to work OK if I hard code the path, however I can’t figure out how to do this where specifying the path using msoFileDialogFolderPicker.
I have tried a number of variations depending on what I have found online and have got as far as below but stuck. Help appreciated.
Dim xFilepath As Variant Dim xFilename As String xFilepath = Application.FileDialog(msoFileDialogFolderPicker) xFilename = Range("Table9[ProgramID]") & " Product Financial Allocation" & ".xlsb" With xFilepath .Title = "Choose Destination" .SHOW mypath = .SelectedItems(1) & "" End With mypath = mypath Sheets("FILES").Range("A3").Select For i = 1 To 3 Sheets("FILES").Range("A" & i).copy Sheets("TEMPLATE").Range("Table9[ProgramID]") ActiveWorkbook.SaveCopyAs Filename:=mypath & Filename FileFormat = 50 Application.DisplayAlerts = False Application.EnableEvents = False Application.ScreenUpdating = False Workbooks.Open Filename:=mypath & Filename Call DeleteQueries ActiveWorkbook.Save ActiveWorkbook.Close Next i Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub