VBA Excel How to insert a predetermined row of headers

Question

I'm having a hard time looking up what I am trying to do because I am having a hard time putting what I am trying to do into words.

Basically, what I am trying to do is take a set of headers (maybe 10-12 columns long) and, in a macro, I want to paste those headers over (replace) the current headers in row 1 of sheet 1. The way I currently have things set up is that my data is being inserted starting at row 2 of every sheet when that sheet is generated. I have a macro written that will copy row 1 of sheet 1 into every existing sheet, however, I need to not have to manually insert the first sheet's headers (it should be part of the macro).

So I guess I need assistance in how to insert a row of predetermined headers over row 1 of sheet 1 ( I want to write the actual header names into the macro code).

EDIT:

Application.CutCopyMode = True

Dim Counter As Long

Counter = Sheets.Count
For i = 1 To Counter
    Sheets("Sheet1").Cells(1, 1).EntireRow.Copy
    Sheets(i).Cells(1, 1).PasteSpecial

Next i

Application.CutCopyMode = False

This what I have for copying code across and this works fine. What I want is to be able to put in a line of code that basically says 'Paste "Header 1" "Header 2" "Header 3" etc' into the corresponding columns of row 1 sheet 1. So I have my header names already picked out but I want to put them directly into the macro code. So if my headers are Apply Banana Lettuce Tomato, I want to put those words into the macro code and have them pasted in sheet 1 row 1 before my copy-paste code listed above.

Answer 1

How to write sheet headers by one code line only

Instead of looping through each title in the headers array, one can write the whole array to a given cell start (e.g. A1) via

    ws.Range("A1").Resize(1, UBound(headers) + 1) = headers 

where the target range will be resized to 1 row and the correct column count via the UBound function; as headers is a zero-bound array, just add +1 to get all titles.

Sub WriteHeaders()
Dim headers(), ws As Worksheet
headers() = Array("Superhero", "City", "State", "Country", "Publisher", "Demographics", _
                  "Planet", "Flying Abilities", "Vehicle", "Sidekick", "Powers")
For Each ws In ThisWorkbook.Worksheets
    .Rows(1) = vbNullString                                 ' clear header row
    .Range("A1").Resize(1, UBound(headers) + 1) = headers   ' write headers into row 1
Next ws
End Sub

Some coding variations (edited due to questions in comment thx Bruce)

Note: I changed your questions for systematic reasons, but tried to include all.

Do you get an error if say headers() was five items, and I did .Range("A1:B1") = headers?

• No, you don't, it just fills the first two items from headers into the indicated cells. So you can use such a construction to reduce a predefined full set of elements to a given number of cells without redimming the (1-dim) array. Personally I prefer resizing as demonstrated, e.g. via .Range("A1").Resize(1,2) = headers.

Fill a vertical target range

• If on the other hand you code .Range("A1:A2") = headers you have to keep in mind, that this is a flat (1-dimensioned) array which you want to force into a column range - you'll get only the first identical header item in both cells.
• To make this work for the available headers here, you'd have to transpose the flat array to a vertical one as follows: .Range("A1:A2") = Application.Transpose(headers) and you'll get at least two of five array items.

Likewise, if I did .Range("A1:AA1") = headers would it a) just fill in the first five items and b) leave the rest blank?

• a) Yes, as in the example above, any available item gets filled in,

• b) no, the cells after complete Input of headers is filled by error #N/A (not available) entries.

• Filling a vertical range would need to transpose as well, as by Range"A1:A100") = Application.Transpose(headers) getting the same #N/A entries after five header items.

Answer 2

Since you know your headers ahead of time, you can switch them out with mine below. This should take the headers you enter, and will paste them into all sheets. NOTE: This will also REMOVE any information in row 1 on any sheet, so if you don't want that to happen, comment/delete the line.

Sub AddHeaders()
Dim headers() As Variant
Dim ws As Worksheet
Dim wb As Workbook

Application.ScreenUpdating = False 'turn this off for the macro to run a little faster

Set wb = ActiveWorkbook

headers() = Array("Superhero", "City", "State", "Country", "Publisher", "Demographics", _
    "Planet", "Flying Abilities", "Vehicle", "Sidekick", "Powers")
For Each ws In wb.Sheets
    With ws
    .Rows(1).Value = "" 'This will clear out row 1
    For i = LBound(headers()) To UBound(headers())
        .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = True
    End With
Next ws

Application.ScreenUpdating = True 'turn it back on

MsgBox ("Done!")

End Sub

You can also use that as a general macro - it prevents needing copy/paste (which is a good thing to learn in VBA, to avoid copy/paste), and you can also create a dynamic header array, if your headers change often. Let me know if this is on track for what you're looking for!

Edit: Note about arrays. By default, an array that has 5 items will be indexed from 0 to 4. I.e. If you do this loop (psuedocode):

myArray() = Array("Batman","Superman","Catwoman","Rorschach")
for i = 1 to uBound(myArray())
  debug.print myArray(i)
Next i

It will only print "Superman","Catwoman","Rorschach". This is because "Batman", as the first entry, is the 0 index. Simply replace the for line with this one for i = lBound(myArray()) to uBound(myArray()) to go from the first to last entry. Or, you can use for i = 0 to uBound(...

But you'll notice in that loop, I have .Cells(1, 1+i).... This is because my loop is starting at 0, so I had to add 1 to i so it would start in the second column.

Don't like that Zero-based array (where it starts at 0)? You can start at 1! How? Just do Dim myArray(1 to 10). This will create an array, but starting at 1. Then, this example will print all four people:

for i = 1 to uBound(myArray())
    debug.print myArray(i)
next i

One tip, for looping through an entire array, is to just use lBound(array()) and uBound(array()) which is the first value, and last value, respectively. This will also allow you to do .Cells(1,i).Value ..., since i starts at 1. Does this make sense?

Answer 3

In order to copy the header from Sheet1 to all other sheets automatically, use:

srcSheet = "Sheet1"
For dst = 1 To Sheets.Count
    If Sheets(dst).Name <> srcSheet Then
    Sheets(srcSheet).Rows("1:1").Copy
    Sheets(dst).Range("A1").Select
    ActiveSheet.Paste
    End If
Next

This code iterates through all the sheets, except srcSheet and copies the first row from srcSheet to them

Answer 4

Assuming your headers are in columns A1:J1 on Sheet1, you can use the following to copy them into B1:K1 on Sheet2

Sheets("Sheet1").Range("A1:J1").Copy Sheets("Sheet2").Range("B1:K1")