I use the following code to import multiple worksheets from another workbook and do some processing. The importing time is too long. Can anyone suggest a more efficient way to import? Should I be looking into more information in the source files for copy? Can anyone please suggest a way to speed up the import
function?
I am not a professional developer, so I might have left out some key points to note. Please let me know if I need to look into something else, as well.
Sub SKR_Import()
On Error GoTo errorhandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sht As Worksheet
Set wb1 = ActiveWorkbook
Dim fd As FileDialog
Dim filechosen As Integer
Dim filename As String
Dim i As Long
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.AllowMultiSelect = True
fd.Title = "Select Excel workbooks to import all sheets"
filechosen = fd.Show
If filechosen = -1 Then
For i = 1 To fd.SelectedItems.Count
Set wb2 = Workbooks.Open(fd.SelectedItems(i))
For Each Sht In wb2.Sheets
Sht.Activate
ActiveSheet.Copy after:=wb1.Sheets(wb1.Sheets.Count)
Next Sht
wb2.Close SaveChanges:=False
Next i
End If
wb1.Activate
Application.ScreenUpdating = True
Exit Sub
errorhandler:
msgBox Error, vbCritical, "Error"
wb2.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub