Move data from UserForm to table using table headers

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Please use below:

VBA Code:
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

Board Regular
Joined
Oct 20, 2017
Messages
166
Hi,

Please use below:

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
    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

Board Regular
Joined
Oct 20, 2017
Messages
166
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.

VBA Code:
    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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,011
Members
416,004
Latest member
reitz1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top