Insert a sheet into workbook using the open dialog

[Johnzea]

I would like to import an xlsm sheet with data into my workbook at the end of the last tab and have the new inserted sheet/tab name the xlsm filename being inserted less the path. below is my code but I cannot get it to work right. any thought to what i am missing or have wrong?

I switched to decaf tonight so i'm a little slow....any help is very much appreciated...thank you


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
.Show

fullpath = .SelectedItems.Item(1)
End With

If InStr(fullpath, ".xls") = 0 Then
Exit Sub
End If
Sheets.Add(, Sheets(Sheets.Count)).Name = fullpath

 

[DanteAmor]

Do you need something like this:

Sub Insert_sheet()
  Dim fullpath As Variant, wPath As String, wFile As String, wb As Workbook
  
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    If Not .Show Then Exit Sub
    fullpath = .SelectedItems.Item(1)
  End With
  wPath = Left(fullpath, InStrRev(fullpath, "\") - 1)
  wFile = Mid(fullpath, InStrRev(fullpath, "\") + 1)
  wFile = Left(wFile, InStrRev(wFile, ".") - 1)
  Set wb = Workbooks.Open(fullpath)
  wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = wFile
  wb.Close False
End Sub

 

[Johnzea]

Thank You....exactly what i was looking for....much appreciated Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.