Excel Data Entry Form inputting data into table

0 votes

I've made a data entry form in a sheet, and I want the data to be taken out and put into a table on another sheet.

You can get a better idea of what I'm aiming for from the photographs below.

https://imgur.com/a/LpWAQ

I am aware that in order to do this, I will need to connect a macro to a button.

I looked for a solution on Google and found this code, which reportedly feeds the data into a table. There is a code here;

Sub copyRow()
Dim ws As Worksheet
Dim lRow As Long

' define which worksheet to work on, i.e. replace Sheet1 with the name of your sheet
Set ws = ActiveWorkbook.Sheets("Data Entry")

' determine the last row with content in column A and add one
lRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1

' combine G7 and G8 and copy into column A, next empty row
ws.Range("A" & lRow) = ws.[G7] & " " & ws.[G8]
' copy the other cells into their ranges
ws.Range("C6:F6").Copy ws.Range("B" & lRow)
ws.Range("C7:F7").Copy ws.Range("F" & lRow)
ws.Range("C8:F8").Copy ws.Range("J" & lRow)
ws.Range("C9:F9").Copy ws.Range("N" & lRow)
ws.Range("C10:F10").Copy ws.Range("R" & lRow)
ws.Range("C11:F11").Copy ws.Range("V" & lRow)
ws.Range("C12:F12").Copy ws.Range("Z" & lRow)
ws.Range("G6").Copy ws.Range("AD" & lRow)
ws.[A1].Select
End Sub
Oct 15, 2022 in Others by Kithuzzz
• 38,000 points
1,294 views

1 answer to this question.

0 votes

Solution

  •  Fill in all the fields in your form with some real data.
  •  Start recording a macro.
  •  Insert a new row to your table (right click -> Insert -> Table Rows Above).
  •  Copy the first cell in your form (Asset Tag).
  •  Click the relevant output cell in the output sheet (C5 in Test Log).
  •  Paste the value into the cell.
  •  Repeat steps 4-6 until you are done with all the form fields.
  •  Stop recording the macro.
  •  Right-click the button and assign your macro to it.
  •  Test, fix, and retry.

This allows you to use VBA's macro recording engine, which is a terrific way to get started with the programme even if you don't know anything about it. To ensure that the fixed references the macro creates (such as Test Log'!$C$5) always point to an empty cell, you want to add the row to the table.

Unfortunately, because the macro recorder records everything you do, you frequently need to edit the code to make changes to make it function. However, that's a fantastic approach to begin learning VBA.

answered Oct 15, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
7,949 views
0 votes
1 answer

Downloading Tableau data into Excel (scientific notation column)

Due to Excel's internal floating point numbering ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,600 points
951 views
0 votes
1 answer

Convert PDF into excel data

To convert data from an image in ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
700 views
0 votes
1 answer

Indent table pasted from Excel into Word

It is feasible to pick up the ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,600 points
558 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,251 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,694 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
959 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,084 views
0 votes
1 answer

Protect excel sheet but allow data entry with form

Try this: Private Sub Workbook_Open() ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,600 points
1,591 views
0 votes
1 answer

Data entry into web page from excel

Reading the File in PHP Example 1 How to ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,600 points
1,194 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP