Copy Table Data to New Worksheet with VBA

How to copy table data to a new worksheet using VBA. In the process we create a new worksheet and paste the data with the original formats.

Watch the training video:

Watch this video on YouTube.

Here’s the complete VBA code to copy table data to another worksheet:

Sub CopyTableData2NewWorksheet()
‘Let’s declare a few needed variables
Dim New_Wksht As Worksheet
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim CopyFormats As Variant
Dim wkshtName As String

Set ACell = ActiveCell
On Error Resume Next
ActiveCellInTable = (ACell.ListObject.Name <> “”)

If ActiveCellInTable = True Then

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ACell.ListObject.Range.Copy

‘We add a new Worksheet after the active sheet
Set New_Wksht = Worksheets.Add(after:=Sheets(ActiveSheet.Index))

‘Ask for the Worksheet name
sheetName = InputBox(“Enter a name for the new worksheet?”, “Name New Sheet”)
New_Wksht.Name = sheetName

With New_Wksht.Range(“A1”)
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End With

‘Create a table of the pasted data
Application.ScreenUpdating = True
Application.CommandBars.FindControl(ID:=7193).Execute
New_Wksht.Range(“A1”).Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Else
MsgBox “Before running the macro select a cell in your List or Table “, _
vbOKOnly, “Copy to new worksheet”
End If
End Sub

Here is a list of some of the Format Control IDs:

7000 Use AutoFormat
7006 Speak Cells
7007 Stop Speaking
7008 By Rows
7009 By Columns
7010 Speak On Enter
7011 Show Text To Speech Toolbar
7067 Scale Drawing
7070 Relative to Organization Chart
7071 Relative to Diagram
7193 Create Table…
7343 Research…
7372 Totals Row
7373 Table Rows Above
7374 Table Rows
7375 Convert to Range
7380 Print Table
7392 Recipient using Internet Fax Service…
7432 Export…
7433 Import…
7500 Hide Ink Annotations
7502 Eraser
7568 Table Columns to the Left
7569 Table Columns
7674 Delete All Ink Annotations
7683 Unlink List
7684 Publish Table…
7693 XML Source…
7698 Compare Side by Side with Book1
7710 Shared Workspace…
7714 Privacy Options…
7761 Synchronize List
7762 Discard Changes and Refresh
7763 View List on Server
7764 Chart Wizard
7765 Resize Table…

Further reading:

ID List of Format Controls

CommandBar.FindControl Method (Office)