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. However, creating tables with headers takes an extra bit of work. I’ll show you the path of salvation to this problem.
Create a Table with Headers Using Excel VBA: 2 Methods
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.
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.
- 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
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.
Thus you can create an Excel table using a VBA With statement.
Read More:Â Excel VBA to Create Table from Range
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
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 Compare Two Tables for Differences with Excel VBA
Practice Section
Open a new Excel sheet and practice these methods on your own.
Download Practice Workbook
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.
