Thanks Jim!

I don't think there's much difference in the speed but the Status bar text
and hiding the taskbar buttons adds a nice professional touch.

cheers,

Damien

"Jim Cone" wrote in message
...

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("YearPeri od")
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.Worksh eets.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 :-)