Take the 2-minute tour ×

In excel, I have a table of which I want to copy the lay-out to several other tables (that have a different length). Each time I want to change all column titles as well (there are 3 columns in each table). I tried defining a new table style, but the funtcion is too limited for what I want (changing header titles, the second cell of the total last row, etc.).

So I think the best way to do this is by making a macro, but can anyone please help me on how to do this?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I think you should define a new table style.

Then in vba create a for each loop, where you loop through all tables (called ListObjects in vba), and apply the style to each table:

Dim tbl as ListObject

For Each tbl In Sheets("Sheet1").ListObjects 'Change Sheet1 to your worksheet name
    tbl.TableStyle = "TableStyleMedium2" 'Change name to your table style
    tbl.HeaderRowRange.Cells(1, 1) = "Value" 'Header 1st column
    tbl.HeaderRowRange.Cells(1, 2) = "Value" 'Header 2nd column
    tbl.HeaderRowRange.Cells(1, 3) = "Value" 'Header 3rd column
    tbl.TotalsRowRange.Cells(1, 2) = "Value" '2nd cell in totals row
share|improve this answer

Your Answer


