Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Table with Headers Using Excel VBA (2 Methods)

The article will show you how to create a table with headers using Excel VBA. Sometimes, it’s easier to use a VBA command to create tables with headers instead of making them manually. Especially when you need to work with similar types of tables in various sheets of your Excel workbook. Generally, you can create a table using simple VBA code. But creating tables with headers takes an extra bit of work. I’ll show you the path of salvation to this problem.


Download Practice Workbook


2 Ways to Create a Table with Headers Using Excel VBA

Say we made a survey on some people and we want to store some of their personal data in an Excel table. We need the column headers like the following picture.

excel vba create table with headers

We will show you how to create the table with these headers in the following sections of this article.


1. Using ‘VBA With Property’ for Headers Cells

In this section, we are going to show you how to create a table with headers using a With statement. Let’s go through the process below for a better understanding.

Steps:

  • First, go to the Developer Tab and then select Visual Basic.

Using ‘VBA With Property’ for Cells to Create a Table with Headers

  • After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

  • Now, type the following code in the VBA Module.
Option Explicit

Sub CreateTableHeader()
Dim Current_WS As Worksheet
Set Current_WS = ThisWorkbook.Worksheets(1)
Dim mn_TableHeader As ListObject
With Current_WS
.ListObjects.Add(xlSrcRange, .Range("B4:G10"), , xlNo).Name = "Survey_Data"
Set mn_TableHeader = .ListObjects(1)
mn_TableHeader.HeaderRowRange.Cells(1, 1) = "Name"
mn_TableHeader.HeaderRowRange.Cells(1, 2) = "Residence"
mn_TableHeader.HeaderRowRange.Cells(1, 3) = "Gender"
mn_TableHeader.HeaderRowRange.Cells(1, 4) = "Age"
mn_TableHeader.HeaderRowRange.Cells(1, 5) = "Profession"
mn_TableHeader.HeaderRowRange.Cells(1, 6) = "Salary"
End With
End Sub

excel vba create table with headers

Code Explanation

  • First, we used a name for the Sub Procedure.
  • After that, we declared Current_WS As Worksheet and set it as the number 1 worksheet.
  • Next, we declared another variable – mn_TableHeader As ListObject.
Dim Current_WS As Worksheet
Set Current_WS = ThisWorkbook.Worksheets(1)
Dim mn_TableHeader As ListObject
  • Thereafter, we used a With Statement to define the range of the table and its name.
  • Also, we set mn_TableHeader to .ListObjects(1) to define the name of the headers in the following parts of the code.
.ListObjects.Add(xlSrcRange, .Range("B4:G10"), , xlNo).Name = "Survey_Data"
Set mn_TableHeader = .ListObjects(1)
  • Finally, we run the code.
  • Next, go back to your sheet where you want to create the table and run the macro named CreateTableHeader as it is our current Macro.

  • Thereafter, you will see your desired table with headers.

Using ‘VBA With Property’ for Cells to Create a Table with Headers

Thus you can create an Excel table using a VBA With statement.

Read More: Excel VBA to Create Table from Range (6 Examples)


Similar Readings


2. Creating Table with Headers Using VBA Value Property

We can also create tables with headers using the Value property too. Let’s go through the steps below.

Steps:

  • First, follow the steps of Method 1 to open a VBA Module.
  • After that, type the following code in the Module.
Sub CreateTableWithHeader2()
Dim table_name As String
    Range("B4").Value = "Name"
    Range("C4").Value = "Residence"
    Range("D4").Value = "Gender"
    Range("E4").Value = "Age"
    Range("F4").Value = "Profession"
    Range("G4").Value = "Salary"
    Application.CutCopyMode = False
    table_name = "Survey_Data_2"
    ActiveSheet.ListObjects.Add(xlSrcRange, _
    Range("$B$4:$G$11"), , xlYes).Name = table_name
End Sub

Creating Table with Headers Using VBA Value Property

Code Explanation

  • First, we used a name for the Sub Procedure.
  • After that, we declared table_name As String.
  • Next, we used a Value property to define the headers of this table.
  • Later, we define the name of the table and its range.
table_name = "Survey_Data_2"
ActiveSheet.ListObjects.Add(xlSrcRange, _
Range("$B$4:$G$11"), , xlYes).Name = table_name
  • Finally, we run the code.
  • Next, go back to your sheet and run the Macro named CreateTableWithHeader2 as it is the name of our current Macro.

Finally, you will get your Excel table with headers and you can insert the data you wish to.

Thus you can create an Excel table using the VBA Value property.

There’s one thing you need to keep in mind while using this code. If you run this code again to create a new table in another sheet, it will show you an error although you will get your table with headers in the process. The reason for the error is the table name. You can’t create multiple tables with the same name in multiple sheets. So it’s better you change the name of the table in the code before running it again.

Read More: How to Create Excel Table with Row and Column Headers


Practice Section

Open a new Excel sheet and practice these methods on your own.


Conclusion

In the end, we can pull the bottom line by considering that you will learn the basic tips of how to create a table with headers using Excel VBA. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo