excel vba import file with three worksheets

Hi.

I am trying to import a file which has 3 sheets in it into a workbook I already have open using vba. The below works if it only has one sheet but not in this case as it has 3. And I don't know how excel decides why which sheet it imports out of the 3.
I basically want it to prompt me to select the file and import all sheets from that file

Cheers.
sample of code below

//code
Sub Import_files()

Dim fd As FileDialog
Dim FileChosen As Long
Dim i As Long

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Select EngTimeReview file"
fd.AllowMultiSelect = False
fd.InitialView = msoFileDialogViewList
fd.Filters.Clear
fd.Filters.Add "Excel Workbook", "*.xls"
FileChosen = fd.Show
If FileChosen = -1 Then
For i = 1 To fd.SelectedItems.Count
Call ReadDataFromSourceFile(fd.SelectedItems(i))
Next i
End If

End Sub


Private Sub ReadDataFromSourceFile(sSrcFilename As String)

Dim shtDest As Worksheet: Set shtDest = ThisWorkbook.Sheets.Add
Dim wbSrc As Workbook: Set wbSrc = Workbooks.Open(sSrcFilename)

With wbSrc.Sheets(1)
.UsedRange.Copy shtDest.Range(.UsedRange.Address)

On Error Resume Next
shtDest.Name = .Name
On Error GoTo 0
End With

wbSrc.Close SaveChanges:=False
Exit Sub
End Sub

Use a For/Next loop:

Please Login or Register  to view this content.

Hi Tajan.

I got a run time error 91. Object variable or With block variable not set

If i go into debug mode its stopped ion line shtDest = ThisWorkbook.Sheets.Add

it says shtDest = nothing

Thanks

Sorry, it should be:

Please Login or Register  to view this content.

thats great.

works perfectly.

Many thanks