Adding Record to table with Column names and arrays

Question

I'm trying to add VBA code to add a record line in to specific columns but do not know if the table I'm adding to will stay the same. I found out how to add based on column names:

Dim LotTbl As ListObject
Dim newreccord As ListRow

Set LotTbl = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Lot_Data")
Set newreccord = LotTbl.ListRows.Add

With newreccord
    .Range(LotTbl.ListColumns("Lot Num").Index).Value = ActiveSheet.Range("B2")
    .Range(LotTbl.ListColumns("Material").Index).Value = ActiveSheet.Range("B15")
End With

But now I need to transfer a large list but I would like to transpose it to the record. On the record these lines will always stay together columns are named Die 1 to Die 10. I would like to paste starting from Die 1 Column.

    .range(LotTbl.ListColumns("Die 1").index).value = ActiveSheet.Range("E1:E13").value

So the data is in rows but you need it in columns? Note that you mention 10 columns but your Range has 13 values, this doesn't fit. — FunThomas
– FunThomas, Commented Jun 24 at 15:43
Have you tried Transpose? Resize i think too. I'll have a look in a moment — Nathan_Sav
– Nathan_Sav, Commented Jun 24 at 16:10

rotabor · Accepted Answer · 2025-06-24 17:11:43Z

3

So, the solution is to transpose the source range and to resize the target range to match its size to the source:

Option Explicit

Sub sdfsd()
  Dim LotTbl As ListObject
  Set LotTbl = ActiveWorkbook.Worksheets("Sheet1").ListObjects("Lot_Data")
  With LotTbl.ListRows.Add
    .Range(LotTbl.ListColumns("Lot Num").Index) = [B2]
    .Range(LotTbl.ListColumns("Material").Index) = [B15]
    .Range(LotTbl.ListColumns("Die 1").Index).Resize(1, 13) = Application.Transpose([E1:E13])
  End With
End Sub

answered Jun 24 at 17:11

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Record to table with Column names and arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related