VBA LOOP Creating same titles on all worksheets?

Question

I want to put the same on row 1 across all worksheets. How would I create a loop to do this? I am pretty new to VBA. I recorded a macro but its still very long. 18 worksheets need to have this. Here is what the recorded macro looks like.

Sheets("C3 MBEL TET OPIS_CMA").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "PSTRIK"
Range("A1").Select
ActiveCell.FormulaR1C1 = "PRECID"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PEXCH"
Range("J1").Select
ActiveCell.FormulaR1C1 = "PQTY"
Range("G1").Select
ActiveCell.FormulaR1C1 = "PCTYM"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PFC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "PACCT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "PPRTCP"
Range("E1").Select
ActiveCell.FormulaR1C1 = "PSUBTY"
Range("H1").Select
ActiveCell.FormulaR1C1 = "PSBUS"
Range("I1").Select
ActiveCell.FormulaR1C1 = "PBS"

Of course, this is repeated 17 more times except with different worksheet names.

I recommend to read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Aug 06 '18 at 13:25

Pᴇʜ · Accepted Answer · 2018-08-06T13:43:32.513

You can use an array to store the sheet names you want to write the headers, and then loop through that array to write it.

Option Explicit

Public Sub WriteHeaderIntoSheets()
    Dim WorksheetNames As Variant
    WorksheetNames = Array("C3 MBEL TET OPIS_CMA", "Sheet1", "Sheet2", "Sheet3") 'your sheet names

    Dim ws As Variant
    For Each ws In WorksheetNames 
        With Worksheets(ws)
            .Range("F1").FormulaR1C1 = "PSTRIK"
            .Range("A1").FormulaR1C1 = "PRECID"
            .Range("C1").FormulaR1C1 = "PEXCH"
            .Range("J1").FormulaR1C1 = "PQTY"
            .Range("G1").FormulaR1C1 = "PCTYM"
            .Range("D1").FormulaR1C1 = "PFC"
            .Range("B1").FormulaR1C1 = "PACCT"
            .Range("K1").FormulaR1C1 = "PPRTCP"
            .Range("E1").FormulaR1C1 = "PSUBTY"
            .Range("H1").FormulaR1C1 = "PSBUS"
            .Range("I1").FormulaR1C1 = "PBS"
        End With
    Next ws
End Sub

Alternatively you can also use an array for the header row which makes it even faster.

Option Explicit

Public Sub WriteHeaderIntoSheets()
    Dim WorksheetNames As Variant
    WorksheetNames = Array("C3 MBEL TET OPIS_CMA", "Sheet1", "Sheet2", "Sheet3") 'your sheet names

    Dim HeaderRow As Variant 'your header values
    HeaderRow = Array("PRECID", "PACCT", "PEXCH", "PFC", "PSUBTY", "PSTRIK", "PCTYM", "PSBUS", "PBS", "PQTY", "PPRTCP")

    Dim ws As Variant
    For Each ws In WorksheetNames
        Worksheets(ws).Range("A1").Resize(ColumnSize:=1 + UBound(HeaderRow)).Value = HeaderRow
    Next ws
End Sub

Was about to write something similar, but I like your way with going for "For Each ws In Worksheetnames" — Mentos, Aug 06 '18 at 13:35
Added an alternative using an array for the header row too, which makes it even faster. — Pᴇʜ, Aug 06 '18 at 13:44
I was trying a variation of this where you wouldn't have to loop through the sheets - manually you'd select the relevant sheets while holding `Ctrl` and enter the formula. Seems like in VBA the sheets and ranges would still need selecting otherwise you get an `Object doesn't support this property or method` even though `Worksheets(WorksheetNames)` is an array of worksheet objects. — Darren Bartrup-Cook, Aug 06 '18 at 13:51
@DarrenBartrup-Cook Yes, accessing ranges in multiple worksheets at once is not supported. So no shortcut for the loop here. — Pᴇʜ, Aug 06 '18 at 14:48

VBA LOOP Creating same titles on all worksheets?

1 Answers1