Import all sheets from another workbook

[Joseph]

Sweet Hans, thanks.

Would you happen to know of some code I could assign to a command button to do the following:
Open browse window
Import all sheets within the selected workbook in to the active workbook?

Google is giving me nothing, not sure if it's even possible.

[HansV]

I have moved this question to a thread of its own since it is not related to the subject of ListBox Issue.

Try this:

Code: Select all

Sub ImportSheets()
  Dim wbkSrc As Workbook
  Dim wbkTrg As Workbook
  Dim strFileName As String
  strFileName = Application.GetOpenFilename
  If strFileName = "False" Then
    MsgBox "Action canceled", vbInformation
    Exit Sub
  End If
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  ' ***** Optional settings *******************
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' ***** End of optional settings ************
  Set wbkTrg = ActiveWorkbook
  Set wbkSrc = Workbooks.Open(Filename:=strFileName, ReadOnly:=True)
  wbkSrc.Worksheets.Copy After:=wbkTrg.Worksheets(wbkTrg.Worksheets.Count)
  wbkSrc.Close SaveChanges:=False

ExitHandler:
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

[Joseph]

Yes, I do think we (well I) was a bit off track there.

Anyway. This works amazing!!!!!!!!!!!!!