Sheets For/Next loop issue

[mariokonfortov]

Hi,

I have twelve consecutive sheets which have a large amount of identical formatting, formulas and validation. I am keen to be able add and remove these thorugh VBA.

When I try to set up a For / Next loop, it seems to run, but only addresses some of the sheets, even though all exist. The same happens when I loop it up or down the sheet numbers and even when I use sheet names.

I am on Excel 2003.

Dim x As Integer
For x = 1 To 12
With Worksheets(x)
.Range("A14:A44").ClearContents
End With
Next x

Any ideas why? Would an Array work faster?

 

[Joe4]

It looks like you are using the Sheet indexes to move through your sheets. Note that is not necessarily the current order of your sheets. If sheets were move around or deleted, the index may not be in the same order as your sheets appear in.
You can use this code to get the index number of a particular sheet. Check the index number of each of these twelve sheets:

MsgBox Sheets("Sheet3").Index

If they are not in the order you need them to be, is there some other way on identifying these sheets (i.e. do they use similar sheet names)? Otherwise, you might need to create an array of sheet names, and loop through that array (unless you want to write code for each and every sheet!).

 

[mariokonfortov]

Hi Joe,

Thanks for that. I tested and the index numbers are all correct (I had changed the names through the VBA editor).

I am afraid arrays are not my forte. The only way I could get it to work was to select a worksheet, then individually select each range. Is there a better way?

Sub Test()
Worksheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Hide-FormatTemplate")).Select

With Worksheets("Jan")
    .Range("A14:A44,B14:B44,C14:C44,D14:D44,H53:DW83,B53:D83,B53:D83,DX14:EA44,DZ45:EA45").Select 
    Selection.ClearContents
End With

End Sub

 

[Joe4]

Try this:

Sub MyClearMacro()

    Dim MySheets As Variant
    Dim ws As Integer
    
    MySheets = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Hide-FormatTemplate")
    
    For ws = LBound(MySheets) To UBound(MySheets)
        Sheets(MySheets(ws)).Range("A14:A44").ClearContents
    Next ws
    
End Sub

 

[Jonmo1]

Might also try

Sub MyClearMacro()
    Dim i As Long
    For i = 1 To 12
        Sheets(Format(DateSerial(1, i, 1), "mmm")).Range("A14:A44").ClearContents
    Next i
    Sheets("Hide-FormatTemplate").Range("A14:A44").ClearContents
End Sub

 

[mikerickson]

You could use this non-looping approach

With ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", _
                                                "Oct", "Nov", "Dec", "Hide-FormatTemplate"))
    .Item(1).Range("A14:A44").Value = vbNullString
    .FillAcrossSheets .Item(1).Range("A14:A44"), xlFillWithContents
End With

 

[mariokonfortov]

WOW!

Thanks to all three. All work.

I ended up going with Joe's answer, as it will proably mean the lest number of lines of code for all my other formatting.

Thanks again.