2

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.

Improve this question
3
  • Please add your current code (or at least the relevant portion) - what have you tried to create the headers? What about your current attempt is failing? Also, you are mixing terms somewhat - do you want to "insert" the headers [implies to me shifting cells downward] or "paste" them over the current headers [implies to me that whatever is in row 1 will be deleted]. Commented Jul 21, 2015 at 13:29
  • 1
    @Grade'Eh'Bacon Lol. We make a good team don't we! Commented Jul 21, 2015 at 13:32
  • Paste them over is what I want to do. My copy-paste macro works, I just want to be able to put into a macro "Paste these headers over whatever is in row 1 of sheet 1" where 'these headers' are defined in the macro. Commented Jul 21, 2015 at 13:40

4 Answers 4

Reset to default
10

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Yes, this is much more in line with what I was looking for, thank you! I just used the record macro function to hash together a "click here then type this" macro, but what you have provided seems extremely helpful for identifying what I can do with arrays and i values for future projects. Just to make sure, if I were to try and call the first value in an array directly (ie: Superhero), that would correspond to the "0" value, correct? Meaning that city would then be the "1" value in the array?
@ScottT, correct! I will add a note in my OP about arrays for you. Would it be worth showing you how to create a dynamic array? I.e. your Sheet1 has the headers you want to put in all other sheets?
This does make sense and thank you for going over it with me! For now I am pretty solid without dynamic arrays, I believe, but again, thank you so much for going over all this!
@ScottT, happy to help!!
Though an old question, I posted a slim version of your valid and fine solution +1:-) you might be interested in and in order to complete this theme - @BruceWayne
1

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")
Improve this answer

1 Comment

Sorry, I must not have typed this out very clearly originally. My copy macro is working well, I am just trying to also put into the macro the headers that I want to past into row 1 directly into the code (H1, H2, H3, H4 would be defined in the code and placed into column 1, column 2, column 3, etc of row 1 sheet 1)
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.

Improve this answer

3 Comments

Oh, cool. I don't know if I knew you could set a range to an array just like that, clever! Do you get an error if say headers() was five items, and I did .Range("A1:A2") = headers? Or does it just fill in the first two values from headers? (likewise, if I did Range("A1:A100") = headers would it just fill in the first five and leave the rest blank? (...I could test this of course, but just curious if you know off the top of your head)
Already edited my post to answer your interesting questions - @BruceWayne:-)
Awesome answer, thanks so much for the edits, learned a lot today :D
0

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

Improve this answer

2 Comments

Sorry, I must not have typed this out very clearly originally. My copy macro is working well, I am just trying to also put into the macro the headers that I want to past into row 1 directly into the code (H1, H2, H3, H4 would be defined in the code and placed into column 1, column 2, column 3, etc of row 1 sheet 1).
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 Can you please check where the error in this code because we are getting the error

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.