I modified the code somewhat with the intent of speeding it up,
but I can't test it. Even if the speed is not much different,
hiding the workbook buttons in the Taskbar and displaying a
message in the StatusBar will make it seem faster.
1. All variables were declared and two new ones added.
2. I assumed that the code was in workbook
"Operating Report - Southern Summary.xls" and used "ThisWorkbook"
instead, in several places.
3. Like your line labels.
4. I assume the wsExists function is efficiently constructed.
'--
Sub ImportSummaries()
On Error GoTo Hell
Dim wbName As String
Dim PathName As String
Dim YearPeriod As String
Dim WSName As String
Dim wk As String
Dim branch As Excel.Range
Dim NewBook As Excel.Workbook
Dim blnShow As Boolean
Application.ScreenUpdating = False
blnShow = Application.ShowWindowsInTaskbar
Application.ShowWindowsInTaskbar = False
YearPeriod = ThisWorkbook.Worksheets("Summary").Range("YearPeriod")
PathName = "C:\OpsReport\" & YearPeriod & "\"
wk = Worksheets("Summary").Range("Weekno")
For Each branch In Worksheets("Tables").Range("BusAreaList").Cells
wbName = branch.Text & " " & YearPeriod & " wk " & wk & ".xls"
WSName = branch.Text
If wsExists(WSName) Then
Application.DisplayAlerts = False
Worksheets(WSName).Delete
Application.DisplayAlerts = True
End If
If Not CBool(Len(Dir(CStr(PathName & wbName)))) Then
MsgBox "There's no data to import for " & WSName, , "Operating Report"
Else
Application.StatusBar = "Processing " & wbName
Set NewBook = Workbooks.Open(PathName & wbName)
NewBook.Worksheets(WSName).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
NewBook.Close savechanges:=False
End If
Next ' branch
Gout:
Set branch = Nothing
Set NewBook = Nothing
Application.StatusBar = False
Application.ShowWindowsInTaskbar = blnShow
Application.ScreenUpdating = True
Exit Sub
Hell:
MsgBox Err.Description, , "Operating Report"
Resume Gout
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
"Damien McBain"
wrote in message
> Why? Are you trying to make the code run faster?
Yep :-)
Similar Threads
