I'm trying to put together a Worksheet where if I click on a button on top it passes my conditional formatting through, as well as inserting a new line. However I feed through the Table name in the code, so if I try to copy the worksheet or try running the macro on another sheet, it breaks.
I put the code in ** around the changes i'm thinking of, however I'm stuck how to pass it on the Set line.
Help please!!
Example:
Dim ws As Worksheet
**Dim tbl As ListObject**
Set ws = ActiveSheet
**Set tbl = ws.ListObjects(1)** <-- This lets me get the table name if this worksheet is copied.
Dim rng As Range
Set rng = ws.[TableName[ColumnName]]
**Set rng = ws.[tbl[ColumnName]]** <- If I do this it Breaks.
I don't think you can actually use structured referencing in VBA. At least, my efforts at trying to have consistently failed. Therefore I use code as shown below.
Private Sub Test1()
Dim Ws As Worksheet
Dim Rng As Range
Dim Tbl As ListObject
Set Ws = ActiveSheet
Set Tbl = Ws.ListObjects(1)
Set Rng = Tbl.ListColumns("Pair").Range
Debug.Print Rng.Address
End Sub
Private Sub Test2()
Dim Ws As Worksheet
Dim Rng As Range
Dim Tbl As ListObject
Set Ws = ActiveSheet
Set Tbl = Ws.ListObjects(1)
Set Rng = Tbl.DataBodyRange.Columns(2)
Debug.Print Rng.Address
End Sub
In the first example you can use the columns name - replace the actual name with a variable of string data type if you prefer - but you get the ListColumn's range. The second example uses an index number to identify the column but you get the column of the DataBodyRange which doesn't include the header and total rows.