I have two tables named Starting & Ending on two different sheets. I would like to have a macro that clears the starting table and then copies whatever is in the ending table over to the starting table. There are formulas in each table; they're the same format. I need to be able to cycle continuously, but with what I have I'm only able to do it once before I run into errors since the table name changes. I'm not sure if what I have is currently the best way to do it. Currently have this:
Private Sub CommandButton1_Click()
Sub ResetTable()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Starting")
'Delete all table rows except first row
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
'Clear out data from first table row (retaining formulas)
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End Sub
Sub CopyTables()
Worksheets(1).ListObjects("Ending").Range.Copy _
Destination:=Worksheets(2).Range("B1")
End Sub
Private Sub CommandButton1_Click()
Sub ResetTable()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Starting")
'Delete all table rows except first row
With tbl.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
End With
'Clear out data from first table row (retaining formulas)
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
End Sub
Sub CopyTables()
Worksheets(1).ListObjects("Ending").Range.Copy _
Destination:=Worksheets(2).Range("B1")
End Sub