Simple request, how to make my exisitng macro work on all sheets in my workbook.

[Graham52]

Hi there,

I have an existing macro (thanks MrExcel) and it works perfectly on the first sheet, I now need it to do the same for the other 10 sheets in the workbook. I have spent many a frustrating hour trying to do this with no success, I would appreciate any help anyone can give.

Thanks and my macro is here:

Sub NextQote()
Range("B1").Value = Range("B1") + 1
Range("F20").ClearContents
Range("I25").ClearContents
Range("B6").ClearContents
Range("A22").ClearContents
Range("K20").ClearContents
Range("J25").ClearContents
Range("J27").ClearContents
Range("I27").ClearContents
Range("H25").ClearContents
Range("L25").ClearContents
Range("G24").ClearContents
Range("G25").ClearContents
Range("I24").ClearContents

End Sub

 

[MARK858]

Maybe....

Sub NextQote()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("B1").Value = ws.Range("B1") + 1
        ws.Range("F20,B6,A22,K20,J25,J27,I27,H25,L25,G24:G25,I24:I25").ClearContents
    Next
End Sub

 

[MARK858]

Or a bit tidier...

Sub NextQote2()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("B1").Value = ws.Range("B1") + 1
        ws.Range("F20,B6,A22,K20,I27:J27,L25,G24,I24,G25:J25").ClearContents
    Next
End Sub

 

[Graham52]

Or a bit tidier...

Sub NextQote2()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("B1").Value = ws.Range("B1") + 1
        ws.Range("F20,B6,A22,K20,I27:J27,L25,G24,I24,G25:J25").ClearContents
    Next
End Sub

Hi Mark, Thank you for your quick reply but unfortunately I get an error report telling me that the ws.Range("F20,B6,A22,K20,I27:J27,L25,G24,I24,G25:J25").ClearContents is not correct.

 

[MARK858]

The code works without error for me.
Are any of your sheets protected or any of the cells merged?

What exactly does the error message state?

 

[Graham52]

The code works without error for me.
Are any of your sheets protected or any of the cells merged?

What exactly does the error message state?

Run Time Error '1004'
Method 'Range of object '_Worksheet' failed

 

[MARK858]

Are you sure that your sheets aren't protected? run the code below...

Sub Test1()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.ProtectContents = True Then MsgBox ws.Name & " is Protected"
    Next
End Sub

 

[Graham52]

Are you sure that your sheets aren't protected? run the code below...

Sub Test1()     Dim ws As Worksheet     For Each ws In ActiveWorkbook.Worksheets         If ws.ProtectContents = True Then MsgBox ws.Name & " is Protected"     Next End Sub

"Thank you so much Mark, I had two cells merged, I went through the entire sheet and found them. Works perfect!!

 

[MARK858]

Glad that you worked out the issue and welcome to the forum