Hello Harry,
Thank you for your additional piece. I have been working on the error handling and the code works fine but it looks a bit messy, as I am still a novice! and I don't know what would happen if it encountered an error? In addition I don't understand where your 'end piece' of code - finishjob slots in?
This is what I have at the moment. Your help will be appreciated.
Rgs Paddy
Private Sub Temp1Survey()
'Course 1 Survey - MECC Ladies Cumulated Rounds and Shots Course 1 2014
Dim screenUpdateState
Dim calcState
Dim eventsState
'check functionality status
screenUpdateState = Application.ScreenUpdating
calcState = Application.Calculation
eventsState = Application.EnableEvents
'turn off screen updating to stop flicker & increase speed
'turn off automatic recalculating mode
'turn off events processing
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
On Error GoTo leaveGracefully
ActiveSheet.Unprotect
Dim wb As Workbook
Dim ws As Worksheet
Dim rngSummary1 As Range, rngSummary2 As Range, rngSummary3 As Range, rngSummary4 As Range, cell As Range
Dim intCount As Integer
Set wb = ThisWorkbook
Set rngSummary1 = wb.Worksheets("Survey").Range("C7")
Set rngSummary2 = wb.Worksheets("Survey").Range("M7")
Set rngSummary3 = wb.Worksheets("Survey").Range("C8")
Set rngSummary4 = wb.Worksheets("Survey").Range("M8")
rngSummary1.Resize(1, 9).Value = 0
rngSummary2.Resize(1, 9).Value = 0
rngSummary3.Resize(1, 9).Value = 0
rngSummary4.Resize(1, 9).Value = 0
For Each ws In Worksheets
If ws.Name <> "Lady_Players" And ws.Name <> "Results" And ws.Name <> "Template" And ws.Name <> "Survey" Then
For intCount = 0 To 8
rngSummary1.Offset(0, intCount).Value = rngSummary1.Offset(0, intCount).Value + ws.Range("D113").Offset(0, intCount).Value
rngSummary2.Offset(0, intCount).Value = rngSummary2.Offset(0, intCount).Value + ws.Range("N113").Offset(0, intCount).Value
rngSummary3.Offset(0, intCount).Value = rngSummary3.Offset(0, intCount).Value + ws.Range("D114").Offset(0, intCount).Value
rngSummary4.Offset(0, intCount).Value = rngSummary4.Offset(0, intCount).Value + ws.Range("N114").Offset(0, intCount).Value
Next intCount
End If
Next ws
Application.ScreenUpdating = screenUpdateState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
leaveGracefully:
'reinstate functionality
Application.ScreenUpdating = screenUpdateState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
'finishJob:
'Exit Sub
'leaveGracefully:
'Application.ScreenUpdating = screenUpdateState
'Application.Calculation = calcState
'Application.EnableEvents = eventsState
'Resume finishJob
'End Sub