How to do a dynamic table with constant row values in VBA

[TomasTeix]

I´m new using VBA. I´ve tried a few methods to make a dynamic table where when new collumns are added, the values of the rows, continue throughout. But I haven´t found a solution. Any Help?

 

[awoohaw]

can you explain a bit more about what you're trying to do?

 

[Vogateer]

We might need an example here. I'm not sure I understand what you mean by "the values of the rows continue throughout".

 

[TomasTeix]

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
8	8	8	8	8	8
3	3	3	3	3	3

Something like this, where by adding a new column header, and a new columns, the 8s and the 3s continue.

 

[TomasTeix]

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
8	8	8	8	8	8
3	3	3	3	3	3

Something like this, where by adding a new column header, and a new columns, the 8s and the 3s continue.

I´m trying to this now by simply copying the first 8 and 3, to the upcoming Column like this:

Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column


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

the error 438 comes on the line "ActiveSheet.ListObjects("TableName").LastColumn.Select"

any help?

 

[Sanjeev1976]

You can try :

Sub AddData2Col()

Dim ws As Worksheet
Dim LastColumn As Long

Set ws = ThisWorkbook.Sheets("Sheet1")

LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column + 1

ActiveSheet.Range("A2:A3").Copy

Dim rng As Range

Set rng = ws.Range(ws.Cells(2, LastColumn), ws.Cells(ws.Rows.Count, LastColumn))

rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

 

[TomasTeix]

This doesn´t work because I have more than one table in this Sheet. Like this the numbers are implemented into the first cells of the first table