помогите упростить простой макрос

[frantic150]

имеется код. помогите превратить это в цикл. обратите внимание, что столбец J не входит в цикл, т.е. он пропущен

Worksheets(4).Name = Sheets("Âñåãî").Range("C2").Value
    Worksheets(4).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(5).Name = Sheets("Âñåãî").Range("D2").Value
    Worksheets(5).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(6).Name = Sheets("Âñåãî").Range("E2").Value
    Worksheets(6).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(7).Name = Sheets("Âñåãî").Range("F2").Value
    Worksheets(7).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(8).Name = Sheets("Âñåãî").Range("G2").Value
    Worksheets(8).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(9).Name = Sheets("Âñåãî").Range("H2").Value
    Worksheets(9).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(10).Name = Sheets("Âñåãî").Range("I2").Value
    Worksheets(10).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(11).Name = Sheets("Âñåãî").Range("K2").Value
    Worksheets(11).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(12).Name = Sheets("Âñåãî").Range("L2").Value
    Worksheets(12).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(13).Name = Sheets("Âñåãî").Range("M2").Value
    Worksheets(14).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(14).Name = Sheets("Âñåãî").Range("N2").Value
    Worksheets(14).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(15).Name = Sheets("Âñåãî").Range("O2").Value
    Worksheets(15).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(16).Name = Sheets("Âñåãî").Range("P2").Value
    Worksheets(16).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text
    
    Worksheets(17).Name = Sheets("Âñåãî").Range("Q2").Value
    Worksheets(17).PageSetup.RightHeader = Worksheets("Date").Range("B4").Text

[SAS888]

Например, так:

Dim i As Integer, c As Integer
For i = 4 To 17
    With Sheets(i)
        c = IIf(i < 11, 1, 0)
        .Name = Sheets("A^n~a*a~i^").Cells(2, i - c)
        .PageSetup.RightHeader = Sheets("Date").[B4]
    End With
Next

[EducatedFool]

Sub test()
    Dim cell As Range, ra As Range: Application.ScreenUpdating = False
    i = 4: h = Worksheets("Date").Range("B4").Text
    For Each cell In Sheets("Âñåãî").Range("c2:i2,k2:q2").Cells
        Worksheets(i).Name = cell
        Worksheets(i).PageSetup.RightHeader = h
        i = i + 1
    Next cell
End Sub