Enter the Header

Private Sub CommandButton1_Click()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$6:$H$16"), , xlYes).Name = "Sales"
End Sub

Classification of Tables

We can classify the tables into 3 categories

First select data and create table through INSERT TAB

Click on Table then DESIGN tab will display on screen

In DESIGN tab we can find different types table desings, we were classified into

Light - 21

Medium - 28

Dark - 11

Through this program we can design different types of tables, by creating new workbook

Each sheet consists of UNIQUE desing of table

Create - Read - Edit - Delete Table

Download The Workbook

Sub Create_Table()
'www.Tricks12345.com

Dim Sh2 As Worksheet
Set Sh2 = ThisWorkbook.Sheets("sheet2")

Sh2.Range("B3").CurrentRegion.Select

Sh2.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Sales_Data"
Sh2.ListObjects("Sales_Data").TableStyle = "TableStyleDark7"

End Sub

Sub Read_Table()
'www.Tricks12345.com
Dim Sh2 As Worksheet
Set Sh2 = ThisWorkbook.Sheets("Sheet2")

'Select through range method
Sh2.Range("Sales_Data").Select

Max_Columns = Sh2.Range("Sales_Data").Columns.Count
MsgBox "This table consists of " & Max_Columns & " Columns"

' How to connect columns
For i = 1 To Max_Columns
With Range("Sales_Data").Columns(i)
.Select
.ColumnWidth = 15
.Font.Size = 15
.Font.ColorIndex = 5
.Font.Name = "Footlight MT Light"
.HorizontalAlignment = xlCenter
End With
Next

' How to connect rows
Max_Rows = Sh2.Range("Sales_Data").Rows.Count
MsgBox "This table consists of " & Max_Rows & " Rows"

For i = 1 To Max_Rows
With Range("Sales_Data").Rows(i)
.Select
.RowHeight = 20

End With
Next

'Header Formatting
Sh2.ListObjects("Sales_Data").HeaderRowRange.Select
With Selection

.RowHeight = 20
.Font.Size = 15
.Font.ColorIndex = 5
.Interior.ColorIndex = 6
.Font.Name = "Footlight MT Light"
.HorizontalAlignment = xlCenter
End With

End Sub

Sub Add_Delete_Rows_Columns()
'www.Tricks12345.com

Dim Sh2 As Worksheet
Set Sh2 = ThisWorkbook.Sheets("Sheet2")

'To add the row at the last
Sh2.ListObjects("Sales_Data").ListRows.Add

'To add the Column at the last
Sh2.ListObjects("Sales_Data").ListColumns.Add

'Delete first row
Sh2.ListObjects("Sales_Data").ListRows(1).Delete

'Add column in second position
Sh2.ListObjects("Sales_Data").ListColumns.Add 2

With Sh2.ListObjects("Sales_Data").ListColumns(2)
.Name = "Hello"
.DataBodyRange.Value = 15

Max = .DataBodyRange.Cells.Count
For i = 1 To Max
.DataBodyRange.Cells(i).Value = 110 + i
Next

End With

End Sub

Sub Delete_Replace()
'www.Tricks12345.com
On Error Resume Next
Dim Sh2 As Worksheet
Set Sh2 = ThisWorkbook.Sheets("Sheet2")
Sh2.ListObjects("Sales_Data").Delete

' Add values from sheet3
ThisWorkbook.Sheets("Sheet3").Activate
Sheets("Sheet3").Range("A1").CurrentRegion.Select
Selection.Copy Sh2.Range("B3")

End Sub