VBA Loop

[hezcal11]

Hello, I am try to go through a number of worsheets, unhide them, clear certain cell ranges and hide them again. I was trying the following macro but I seem to get an error and the routine stops. Can anyone suggest why.

but I seem to get an error and the routine stops. Can anyone suggest why.

Dim i As Integer
For i = 1 To 50
Worksheets(i).Visible = xlSheetVisible
Worksheets(i).Select
Range("B12:H12,B16:H16,B20:H20,B24:H24,B28:H28").Select
Selection.ClearContents
Range("b12").Select
ActiveSheet.Visible = xlVeryHidden
Range("w7") = i
Next i

End Sub

The line ACTIVESHEET.VISIBLE=XLVERY HIDDEN is highlighted yellow. It seems to work but stops on the sixth cycle.

Thanks

Colin

 

[hezcal11]

Hello, I still have the same issue. Its seems to work ok but I cannot understand why it even goes to the MAIN worksheet in the first place. And it's only certain lines it deletes but always the same ones. Is there any way to run a macro one stage at a time so that I could maybe see where it goes astray. Colin

 

[VoG]

You can use F8 to step through the code Debugging VBA

 

[hezcal11]

OK will try that

 

[hezcal11]

Using F8, there does not appear to be any deviation from the normal routine. It is deleting 6,10,14,18 and 22 on the MAIN worksheet which corresponds to the ranges identified in the macro for the pages 1 to 50. There is not much in the routine which could be going wrong.

 

[VoG]

Maybe

Sub etest()
Dim i As Long
For i = 1 To 50
    If Worksheets(i).Name <> "MAIN" Then Worksheets(i).Range("B12:H12,B16:H16,B20:H20,B24:H24,B28:H28").ClearContents
Next i
End Sub

 

[hezcal11]

Thats it!! Perfect. Thanks for all your help.

Colin