(Solved) – Copy/Import Sheet to This Workbook

(solved)-–-copy/import-sheet-to-this-workbook

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

Leave a Reply

Your email address will not be published. Required fields are marked *