Entering Headings via a Macro in Excel

Handle a range in one go

Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.

This is the result we are after.

I used these headings for a macro I created to identify all the PivotTables in a file – see the whole macro here.

The piece of code that creates the headings is below.

Sub InsertHeadings()
 
Range("A1:D1") = Array("PivotTable Name", "Sheet Name", "Report Range", "Source Data")
 
End Sub

Instead of setting the heading entry in each cell individually, you can use the Array command to populate a range with a series of entries.

You separate the headings with commas. Make sure the number of cells in the range matches the number of entries between the parentheses.

To make the sure the columns are wide enough to display the headings you can add another line of code that applies column autofit to the range.

Sub InsertHeadings()
 
Range("A1:D1") = Array("PivotTable Name", "Sheet Name", "Report Range", "Source Data")
 
Range("A1:D1").EntireColumn.AutoFit
 
End Sub

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.