Is there a better way to repeat the column Titles in Tables?

March 4, 2012 at 08:05:39
Specs: Windows 7
I want to repeat the column Title in each column of a Table instead of copy and pasting each column title into each cell down the list. Keep in mind all of these Cells have information in it. So I have to manually go down the list to insert these Titles into each Cell without overwriting the Cell information.

See More: Is there a better way to repeat the column Titles in Tables?

Report •


#1
March 4, 2012 at 16:49:10
I don't understand what you mean.

You say that you want to repeat column titles in each column, but then you use the words "down the list".

Columns go across, rows go down.

You also say that the cells have "information" in them. What kind of information? Text? Numbers? Formulas?!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 6, 2012 at 16:51:43
I'm 99.9931 percent sure that Columns go down, rows go across.

The cells have words, numbers, symbols in them. Any text characters really.

A column Title is just information in a Cell. So if I have 6 cells across and 6 down then I just want to repeat only the top column Cell of Information to repeat down the columns BUT without overwriting the information in those cells going down. I'm sure the only method that I currently use is to just copy/paste which works well. But thought maybe there's a better way.

Thanks for your help!


Report •

#3
March 7, 2012 at 07:18:07
re: "I'm 99.9931 percent sure that Columns go down, rows go across."

You misunderstood what I meant.

If you look across the top of your sheet, you will see the column labels (A, B, C, etc.) and your Columns Titles in e.g. A1, B1, C1.

If you look down the left hand side of your sheet, you'll see the row numbers (1, 2, 3).

That's what I meant by "Columns go across, rows go down.

As far as what you are trying to do, it can be done either with a macro or a formula.

If you want to use a formula, you could use this process

Let's say you start with this in Sheet 1

     A           B         C
1   Title1   Title2    Title3
2   Info1    Info2      Info3
3   Info4    Info5      Info6

Copy your title row over to Sheet2!A1
In Sheet2!A2, enter this formula:

=A$1 & SHEET1!A2

Drag it down and across.

You should end up with this in Sheet2:

     A                  B                C
1     Title1         Title2           Title3
2   Title1Info1    Title2Info2      Title3Info3
3   Title1Info4    Title2Info5      Title3Info6


If you only want to retain the data afterwards, and not the formula, do a Copy...PasteSpecial...Values

If that doesn't work for you, tell us why and we'll see what else we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 10, 2012 at 06:49:45
I guess I misunderstood you but that's a great follow-up! Thanks. I'll have to try it. I just hope that the information in the cell columns are not overwritten.

Report •

#5
March 10, 2012 at 14:44:49
re: "I just hope that the information in the cell columns are not overwritten.'

Of course, you are going to try this in a back up copy of your workbook.

You should never try *anything* that could damage your data without having a backup copy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 11, 2012 at 00:35:20
Is it that you want the column titles to be repeated on each page when you print this?

You would do "File > Page setup...", then enter the range of rows (eg. "$1" or "$1:$3") with your title info into "Rows to repeat at top:".


Report •

#7
March 12, 2012 at 17:30:31
DerbyDad03, can you create a macro to copy/paste all headers/footers on every page and paste them onto the page they are on as normal text instead of just clicking into each header/footer which kills time?

Forget about the table columns it is not that big of an issue as much as headers/footers are.

I may have to contact a programmer to get a special vb macro since the macro recorder won't allow me to click into headers/footers to record commands.

Nevermind sburtchin, thanks for the help.


Report •

#8
March 12, 2012 at 18:12:50
This code will copy the header into A1 and the footer into A68, then clear both the header and footer.

The header and footer are centered in this case, but it would work just as well with .LeftFooter, .RightHeader, etc.

Sub PutHeaderFooterInCell()
    Range("A1") = ActiveSheet.PageSetup.CenterHeader
    Range("A68") = ActiveSheet.PageSetup.CenterFooter
     ActiveSheet.PageSetup.CenterHeader = ""
     ActiveSheet.PageSetup.CenterFooter = ""
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question