Import all worksheets from a chosen workbook

This code example will present you with a dialog box where you can choose which Excel (.xls or .xlsx) workbook you want to import into your current workbook. It is assuming that you are running this macro from a worksheet named ‘Sheet1’ or you at least have a worksheet named ‘Sheet1’ in the workbook. This can be amended if required in the lower half of the code.

This particular example is relatively simple and straightforward in that it will import all the tabs regardless of whether there is any data in the tab or not. This is particularly useful when you are upgrading from using a lookup on closed workbooks and would prefer the actual data to be present in your current workbook.

This particular code was used in a recent project where the user was using vlookup on 26 separate sheets of a closed workbook for over 78,000 rows of data looking up against 1.7million postal address records. The workbook seemed a little unstable and was quite slow in navigating about. We added a further process following this import, which carried out all the relevant lookups via VBA, ensured the results of the VBA were then stored as text (Copy and Paste Special) and then removed the worksheets that had been added in the first place making the entire process efficient from beginning to end and keeping the overall size of the file as low as possible.

Feel free to leave a comment below, ask a question or contact us using the form and we will get back to you. We can help with your Excel based project or offer guidance in the right direction.

Private Sub Import_all_sheets_from_workbook
Dim sImportFile As String
Dim wkbImport As Workbook
Dim wkbThisBk As Workbook
Dim sht As Worksheet
Dim vFilename As Variant
Dim zFilter As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wkbThisBk = ActiveWorkbook
zFilter = "Microsoft Excel Workbooks, *.xls; *.xml; *.xlsx"
vFilename = Application.GetOpenFilename( _
FileFilter:=zFilter, Title:="Open Workbooks", _

If vFilename = "False" Then

MsgBox "No File Selected!"
Exit Sub

Set wkbImport = Application.Workbooks.Open(Filename:=vFilename)

For Each sht In wkbImport.Sheets
sht.Copy After:=wkbThisBk.Sheets("Sheet1")
Next sht

wkbImport.Close SaveChanges:=False

End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us


Leave a Reply

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