How to create Excel Table with the current sheet name using VBA

This VBA Code allows you to create Excel Table with the current sheet name using VBA

Usually, when we convert the data into Excel tables by default it is given with the name as “Table1” and later we need to manually rename the table name. With this VBA code, it will take the current sheet name and update it automatically when we convert it to Excel Table.

You can also check my previous blog or YouTube video where I have written VBA code to convert all the data to Excel Tables in a workbook.

Video

Check out the video for easy demonstration

Subscribe to YouTube Channel for Exciting Tips & Tricks On Office Applications

You can use the code in Excel for mac & windows

1. Copy the below VBA Code by clicking on the copy box
2. Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
   1. mac users fn  + ⌥ [option] + F11 
   2. windows users Alt  + F11
3. From the menu bar select Insert > Module and insert new Modules
4. Paste the code into the module.

Note: It is always to take a back-up copy of your original before running the code on the original file.

Sub ConvertActiveSheetDataToExcelTables()
  ' Convert Active Sheet Data To Excel Tables with current sheet name
  ' Coded By : Faraz Shaikh
  ' Work With: Excel for Mac & Windows
  ' Updated  : 20210803
  ' Website  : www.ExcelExciting.com
  ' YouTube  : https://youtube.com/ExcelExciting
  ' Facebook : https://facebook.com/ExcelExciting
        Range("A1").CurrentRegion.Select
        If ActiveSheet.ListObjects.Count < 1 Then
            ActiveSheet.ListObjects.Add.Name = ActiveSheet.Name
        End If
        
        MsgBox "Active Worksheet Sucessfully Converted To Excel Tables", vbInformation, "Faraz Shaikh | www.ExcelExciting.com"
    
End Sub

Bonus Solution from Wayne Edmondson

This solution was proposed by one of my subscribers “Wayne Edmondson”. who modified the code to convert the data in the CurrentRegion of the current selection. So, it does not tie you to a table starting from A1. It’s a small adjustment. You must have the pointer inside the range of data before running the macro.

  ' Convert Active Sheet Data To Excel Tables with current sheet name without referencing the range.
  ' Coded By : Wayne Edmondson
  ' Work With: Excel for Mac & Windows
  ' Updated  : 20210807
  ' Website  : www.ExcelExciting.com
  ' YouTube  : https://youtube.com/ExcelExciting
  ' Facebook : https://facebook.com/ExcelExciting
  
Sub ConvertToTable()
  
  If ActiveSheet.ListObjects.Count < 1 Then 
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes).Name = ActiveSheet.Name 
  End If
          
  MsgBox "Active Worksheet Sucessfully Converted To Excel Tables", vbInformation, "Faraz Shaikh | www.ExcelExciting.com"
    
End Sub

Was the information helpful in this blog?

• If yes, hit that share button and show the excitement to the world.
• Subscribe to our free posts.

If no, please let us know what to improve.

Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.