VBA Create a table

[thedeadzeds]

Hi Guys,

I have recorded a macro to create a table which works fine but i'm wondering if there is a way to change the range so it looks to the last row in column P rather than the selected range. Hope this makes sense.

Thanks

Sub CREATE()

    Sheets("Bethan").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$18:$P$3020"), , xlYes).NAME _
        = "Table8"
    Range("Table8[#All]").Select
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table8"), "Dealer"). _
        Slicers.Add ActiveSheet, , "Dealer 1", "Dealer", 9, 583.5, 144, 198.75
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table8"), _
        "description").Slicers.Add ActiveSheet, , "description", "description", 46.5, _
        621, 144, 198.75
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table8"), "Event"). _
        Slicers.Add ActiveSheet, , "Event 1", "Event", 84, 658.5, 144, 198.75
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table8"), _
        "Additional Work").Slicers.Add ActiveSheet, , "Additional Work", _
        "Additional Work", 121.5, 696, 144, 198.75
    ActiveSheet.Shapes.Range(Array("Additional Work")).Select
    ActiveSheet.Shapes.Range(Array("Dealer 1")).Select
    Range("J2:L2").Select
    Range("L2").Activate
    ActiveSheet.Shapes.Range(Array("Dealer 1")).Select
    ActiveSheet.Shapes("Dealer 1").IncrementLeft -556.5
    ActiveSheet.Shapes("Dealer 1").IncrementTop 8.25
    ActiveSheet.Shapes.Range(Array("description")).Select
    ActiveSheet.Shapes("description").IncrementLeft -390.75
    ActiveSheet.Shapes("description").IncrementTop -28.5
    ActiveSheet.Shapes.Range(Array("Event 1")).Select
    ActiveSheet.Shapes("Event 1").IncrementLeft -198.75
    ActiveSheet.Shapes("Event 1").IncrementTop -66
    ActiveSheet.Shapes.Range(Array("Additional Work")).Select
    ActiveSheet.Shapes("Additional Work").IncrementLeft 3
    ActiveSheet.Shapes("Additional Work").IncrementTop -103.5
    Range("Table8[[#Headers],[Dealer]]").Select
    ActiveSheet.ListObjects("Table8").NAME = "Bethan"
End Sub

 

[ParamRay]

Replace line #5 with:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("B18:P" & Cells(Rows.Count, "P").End(xlUp).Row)).Name = "Table8"

 

[Norie]

Try this.

With Sheets("Bethan")
    .ListObjects.Add(xlSrcRange, .Range("$B$18", .Range("P" & Rows.Count).End(xlUp)), , xlYes).Name = "Table8"
End With