Move data from UserForm to table using table headers

[K1600]

I have an userform to input data into my Excel table (Table8) and I am currently using the below code to move the data from the UserForm to the table. Is there a way that I can move the data into the relevant location in the table by using the header name for each column rather than listing the column by number?

Thanks in advance.

Private Sub CommandButton1_Click()

'Copy input values to sheet

Dim lRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.VRM.Value
        .Cells(lRow, 2).Value = Me.MOTExp.Value
        .Cells(lRow, 3).Value = Me.Car.Value
        .Cells(lRow, 4).Value = Me.Make.Value
        .Cells(lRow, 5).Value = Me.Complete.Value
    End With

'Clear input controls
Me.VRM.Value = ""
Me.MOTExp.Value = ""
Me.Car.Value = ""
Me.Make.Value = ""
Me.Complete.Value = ""

End Sub

 

[Saurabhj]

Hi,

Please use below:

with ws.ListObjects("tablename")
            .ListColumns("Col1name").Range(lRow) = Me.VRM.Value
            .ListColumns("Col2name").Range(lRow) = Me.MOTExp.Value
            .ListColumns("Col3name").Range(lRow) = Me.Car.Value
            .ListColumns("Col4name").Range(lRow) = Me.Make.Value
            .ListColumns("Col5name").Range(lRow) = Me.Complete.Value
End With

 

[K1600]

Hi,

Please use below:

with ws.ListObjects("tablename")
            .ListColumns("Col1name").Range(lRow) = Me.VRM.Value
            .ListColumns("Col2name").Range(lRow) = Me.MOTExp.Value
            .ListColumns("Col3name").Range(lRow) = Me.Car.Value
            .ListColumns("Col4name").Range(lRow) = Me.Make.Value
            .ListColumns("Col5name").Range(lRow) = Me.Complete.Value
End With

Hi Saurabhj,

That's great, thanks very much for your help.

 

[GWteB]

Using the below alternative, it is not necessary to determine the last row used / the next empty row. Code works regardless the location of your table on your worksheet.

    Dim ws As Worksheet, r As ListRow
    
    Set ws = Worksheets("Sheet1")
    
    With ws.ListObjects("Table8")
        Set r = .ListRows.Add
        Intersect(r.Range, .ListColumns("VRM").Range).Value = Me.VRM.Value
        Intersect(r.Range, .ListColumns("MOTExp").Range).Value = Me.MOTExp.Value
        Intersect(r.Range, .ListColumns("Car").Range).Value = Me.Car.Value
        Intersect(r.Range, .ListColumns("Make").Range).Value = Me.Make.Value
        Intersect(r.Range, .ListColumns("Complete").Range).Value = Me.Complete.Value
    End With

 

[K1600]

Using the below alternative, it is not necessary to determine the last row used / the next empty row. Code works regardless the location of your table on your worksheet.

    Dim ws As Worksheet, r As ListRow
   
    Set ws = Worksheets("Sheet1")
   
    With ws.ListObjects("Table8")
        Set r = .ListRows.Add
        Intersect(r.Range, .ListColumns("VRM").Range).Value = Me.VRM.Value
        Intersect(r.Range, .ListColumns("MOTExp").Range).Value = Me.MOTExp.Value
        Intersect(r.Range, .ListColumns("Car").Range).Value = Me.Car.Value
        Intersect(r.Range, .ListColumns("Make").Range).Value = Me.Make.Value
        Intersect(r.Range, .ListColumns("Complete").Range).Value = Me.Complete.Value
    End With

Thanks for that GWteB, I have tweaked it to include this as I am possibly going to have another table on the page at some point too.

Kind regards, G