VBA applying a sub to worksheets

[Pinaceous]

Hi ALL!

I'm trying to apply this sub to worksheets(4,6,8,10,12,14,16,18,20,22,24,26).

I figure I can loop through this sub until it completes this task or somehow tell vba to do only these worksheets.

Sub FormatWorkSheet()


    Range("L11:L12").NumberFormat = "###,###,##0"
    Range("L13").NumberFormat = "###,###,##0.000 ""KG"""
    Range("L14").NumberFormat = "###,###,##0"
    Range("L15").NumberFormat = "###,###,##0.000 ""KG"""
    Range("L16").NumberFormat = "###,###,##0"
    Range("L17").NumberFormat = "###,###,##0.000 ""KG"""
    Range("L18").NumberFormat = "###,###,##0"
    Range("L19").NumberFormat = "###,###,##0.000 ""KG"""
    Range("L20").NumberFormat = "###,###,##0"

Can someone please help me with this code?

Thank you!
pinaceous

 

[StephenCrump]

I assume you have sheet names 4, 6, 8 etc? Rather than wanting to format the 4th, 6th, 8th etc worksheet?

Try :

Sub Test()

    Dim i As Long
    
    For i = 4 To 26 Step 2
        Call FormatWorkSheet(CStr(i))
    Next i

End Sub
Sub FormatWorkSheet(s As String)

    With Worksheets(s)
        .Range("L11:L12,L14,L16,L18,L20").NumberFormat = "###,###,##0"
        .Range("L13,L15,L17,L19").NumberFormat = "###,###,##0.000 ""KG"""
    End With
    
End Sub

 

[Pinaceous]

I assume you have sheet names 4, 6, 8 etc? Rather than wanting to format the 4th, 6th, 8th etc worksheet?

Hi StephenCrump,

Actually, the sheet names do differ from the worksheet names.

For example, my:

worksheet(4) = Sheet2
worksheet(6) = Sheet6
worksheet(8) = Sheet8
worksheet(10) = Sheet10
worksheet(12) = Sheet12
worksheet(14) = Sheet14
worksheet(16) = Sheet16
worksheet(18) = Sheet18
worksheet(20) = Sheet20
worksheet(22) = Sheet22
worksheet(24) = Sheet24
worksheet(26) = Sheet26

I am getting a VBA Run-time error '9':

Can you please help me fix this?

Thank you,
pinaceous

 

[StephenCrump]

If you want Worksheets(4), Worksheets(6) ... etc, as opposed to Worksheets("4"), Worksheets("6") ... etc, then

'Change

Call FormatWorkSheet(CStr(i))

'To

Call FormatWorkSheet(Worksheets(i).Name)

You're aware that the worksheet order may change if you start inserting/deleting worksheets?

 

[Pinaceous]

You're aware that the worksheet order may change if you start inserting/deleting worksheets?

Hi StephenCrump,

Yes, I am aware of this fact.

The code does work but I have to be on each worksheet, then run the code for it to work.

Does it work for you all at the same time or separate?

Thank you!
Pinaceous

 

[StephenCrump]

The code does work but I have to be on each worksheet, then run the code for it to work.

Sorry, I may not have been clear. Complete code loop, which will run regardless of what the ActiveSheet is:

Sub Test()

    Dim i As Long
    
    For i = 4 To 26 Step 2
        Call FormatWorkSheet(Worksheets(i).Name)
    Next i

End Sub
Sub FormatWorkSheet(s As String)

    With Worksheets(s)
        .Range("L11:L12,L14,L16,L18,L20").NumberFormat = "###,###,##0"
        .Range("L13,L15,L17,L19").NumberFormat = "###,###,##0.000 ""KG"""
    End With
    
End Sub

 

[rlv01]

Any chance you can do it all in one sub?

Sub RPTTest()
    Dim WS As Worksheet
    
    For Each WS In Worksheets
            Select Case WS.Index
                Case 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26
                    WS.Range("L11:L20").NumberFormat = "###,###,##0"
                    WS.Range("L13,L15,L17,L19").NumberFormat = "###,###,##0.000 ""KG"""
            End Select
    Next WS
End Sub

 

[Pinaceous]

Any chance you can do it all in one sub?

Sub RPTTest()
    Dim WS As Worksheet
   
    For Each WS In Worksheets
            Select Case WS.Index
                Case 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26
                    WS.Range("L11:L20").NumberFormat = "###,###,##0"
                    WS.Range("L13,L15,L17,L19").NumberFormat = "###,###,##0.000 ""KG"""
            End Select
    Next WS
End Sub

Thank you!

 

[Pinaceous]

Sorry, I may not have been clear. Complete code loop, which will run regardless of what the ActiveSheet is:

Sub Test()

    Dim i As Long
   
    For i = 4 To 26 Step 2
        Call FormatWorkSheet(Worksheets(i).Name)
    Next i

End Sub
Sub FormatWorkSheet(s As String)

    With Worksheets(s)
        .Range("L11:L12,L14,L16,L18,L20").NumberFormat = "###,###,##0"
        .Range("L13,L15,L17,L19").NumberFormat = "###,###,##0.000 ""KG"""
    End With
   
End Sub

Thank you!