VBA Help

[brinks19]

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

 

[Domenic]

You can refer to your table by index number. For example, assuming that the active sheet contains only one table, you can refer to it as follows...

[COLOR=#333333]Set tbl = ActiveSheet.ListObjects(1)[/COLOR]

Hope this helps!

 

[brinks19]

This works, thank you!!