It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.

Let’s say we want to label row 1 with First Name, Last Name, Gender and State in four separate columns. The simple VBA command that performs this is

Range("A1:D1") = Array("First Name", "Last Name", "Gender", "State")

This inserts the entries on the active sheet – see below.

If you wanted to run it on a specific sheet you would use the sheet codename as below

Sheet1.Range("A1:D1") = Array("First Name", "Last Name", "Gender", "State")

The sheet codename is the name VBA uses in the Project window on the left of the code. See image below

Using the codename gets around the issue of users changing sheet tab name.

To change the column width to match the headings you could use the following lines of code

Range("A1:D1").EntireColumn.AutoFit

Or

Sheet1.Range("A1:D1").EntireColumn.AutoFit