Run Time Error 1004: Please Help
 

The following produces the run time error, even though
that part is an actual copy of a recorded macro.
What am I missing?

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxx")
End If

Next
ActiveWorkbook.Unprotect (["xxx1"])

Sheets("Options").Select
THIS is were the error shows up
Range("C6").Select
Selection.ClearContents
Range("H6:I6").Select
Selection.ClearContents

Sheets("Pricing").Select
Range("C120").Select
Selection.ClearContents
Range("C121").Select
Selection.ClearContents

Sheets("Contract").Select
Range("B1").Select

If ws.ProtectContents = False Then
ws.Protect ("xxx")
End If

Application.ScreenUpdating = True

End Sub

Run Time Error 1004: Please Help
 

It may (or may not, but it is worth a try) help to clean up the select
satements that you don't need...

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxx")
End If

Next
ActiveWorkbook.Unprotect (["xxx1"])

with Sheets("Options")
THIS is were the error shows up
.Range("C6").ClearContents
.Range("H6:I6").ClearContents
end with
with Sheets("Pricing")
.Range("C120:C121").ClearContents
end with

Sheets("Contract").Select
Range("B1").Select
'not sure what you are doing here? ws is which sheet?
If ws.ProtectContents = False Then
ws.Protect ("xxx")
End If

Application.ScreenUpdating = True

End Sub

--
HTH...

Jim Thomlinson


"BEEJAY" wrote:

The following produces the run time error, even though
that part is an actual copy of a recorded macro.
What am I missing?

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxx")
End If

Next
ActiveWorkbook.Unprotect (["xxx1"])

Sheets("Options").Select
THIS is were the error shows up
Range("C6").Select
Selection.ClearContents
Range("H6:I6").Select
Selection.ClearContents

Sheets("Pricing").Select
Range("C120").Select
Selection.ClearContents
Range("C121").Select
Selection.ClearContents

Sheets("Contract").Select
Range("B1").Select

If ws.ProtectContents = False Then
ws.Protect ("xxx")
End If

Application.ScreenUpdating = True

End Sub

Run Time Error 1004: Please Help
 

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxx")
End If
Next
ActiveWorkbook.Unprotect ("xxx1")

WITH Sheets("Options")
''''' THIS is were the error shows up
.Range("C6").ClearContents
.Range("H6:I6").ClearContents
END WITH

Sheets("Pricing").Range("C120:C121").ClearContents

Sheets("Contract").ACTIVATE
Range("B1").Select


If ws.ProtectContents = False Then
ws.Protect ("xxx")
End If

Application.ScreenUpdating = True

End Sub

Run Time Error 1004: Please Help
 

Thanks ALL for your help.
I still ended up with an error at the same spot, till I changed the actual
Range.
My working final is as follows:

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxxx")
End If
Next
ActiveWorkbook.Unprotect (["xxx1"])

Sheets("Options").Range("C6:I6").ClearContents
Sheets("Pricing").Range("C120:C121").ClearContents

Sheets("Contract").Activate
Range("B1").Select

For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = False Then
ws.Protect ("xxxx")
End If
Next
ActiveWorkbook.Protect (["xxx1"])
Application.ScreenUpdating = True
End Sub

"Patrick Molloy" wrote:

Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxx")
End If
Next
ActiveWorkbook.Unprotect ("xxx1")

WITH Sheets("Options")
''''' THIS is were the error shows up
.Range("C6").ClearContents
.Range("H6:I6").ClearContents
END WITH

Sheets("Pricing").Range("C120:C121").ClearContents

Sheets("Contract").ACTIVATE
Range("B1").Select


If ws.ProtectContents = False Then
ws.Protect ("xxx")
End If

Application.ScreenUpdating = True

End Sub