Selecting Last Column Header of a Table to paste into

[TomasTeix]

Hi! i´ve been struggling with this one for a bit now. I want to paste something into the Last Column Header of a Table. I´ve tried with following code:

ActiveSheet.ListObjects("TableName").HeaderRowRange(LastColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I´´ve verified if I was in the active sheet, and if the table name was correct already. By testing this code, it pastes on the cell before the First Column header, and not on the last column header.
Any help?

 

[Georgiboy]

If you want to change the value of the last column in the header then perhaps the below will help:

Sub test()
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    ws.Range("A1").Copy
    
    ws.ListObjects("TableName").HeaderRowRange.End(xlToRight).PasteSpecial xlPasteValues
End Sub

 

[TomasTeix]

If you want to change the value of the last column in the header then perhaps the below will help:

Sub test()
    Dim ws As Worksheet
   
    Set ws = ActiveSheet
    ws.Range("A1").Copy
   
    ws.ListObjects("TableName").HeaderRowRange.End(xlToRight).PasteSpecial xlPasteValues
End Sub

THANKS

 

[TomasTeix]

If you want to change the value of the last column in the header then perhaps the below will help:

Sub test()
    Dim ws As Worksheet
   
    Set ws = ActiveSheet
    ws.Range("A1").Copy
   
    ws.ListObjects("TableName").HeaderRowRange.End(xlToRight).PasteSpecial xlPasteValues
End Sub

okay now I have a new challange. How do I make this so that the tables becomes dynamically growing?

ActiveSheet.ListObjects("TableName").Cells(1, Columns.Count).End(xlToRight).PasteSpecial xlPasteValues

Why doesn´t this work?

 

[Georgiboy]

If you want to paste into the cell to the right of the last header cell to make a new column then you can try the below:

Sub test()
    Dim ws As Worksheet
   
    Set ws = ActiveSheet
    ws.Range("A1").Copy
   
    ws.ListObjects("TableName").HeaderRowRange.End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
End Sub

 

[TomasTeix]

If you want to paste into the cell to the right of the last header cell to make a new column then you can try the below:

Sub test()
    Dim ws As Worksheet
  
    Set ws = ActiveSheet
    ws.Range("A1").Copy
  
    ws.ListObjects("TableName").HeaderRowRange.End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
End Sub

UR A LEGEND