I have this code and I am trying to make a general VBA such that it will ask me for an Excel file that I want to copy a sheet from. I need help making it more innovative. After I select the Excel file, I want it to list all the tabs available from the file selected and allow me to select those sheets that I want to import.
Sub CopySheet() Application.ScreenUpdating = False Dim flder As FileDialog Dim FileName As String Dim FileChosen As Integer Dim wkbSource As Workbook Dim wkbDest As Workbook Set wkbDest = ThisWorkbook OpenFile: Set flder = Application.FileDialog(msoFileDialogFilePicker) flder.Title = "Please Select an Excel File" flder.InitialFileName = "c:" flder.InitialView = msoFileDialogViewSmallIcons flder.Filters.Clear flder.Filters.Add "Excel Files" MsgBox ("Select a folder and then a file to open.") FileChosen = flder.Show FileName = flder.SelectedItems(1) Set wkbSource = Workbooks.Open(FileName) wkbSource.Sheets("Sheet1").UsedRange.Copy wkbDest.Sheets("Master").Cells(wkbDest.Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues Application.CutCopyMode = False Application.ScreenUpdating = True wkbSource.Close savechanges:=False If MsgBox("Do you want to open another workbook?", vbYesNo) = vbYes Then GoTo OpenFile End Sub
This part will need to be replaced.
wkbSource.Sheets("Sheet1").UsedRange.Copy wkbDest.Sheets("Master").Cells(wkbDest.Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial