efficient data entry

**cporter5** · 01-23-2010, 07:24 PM

We ( my small company and myself) are about to start a project that will involve adding a lot of data into excel sheets ... we are trying to figure out the most time efficient way to do this is there a way to make an excel sheet work something like this

Have a few lines at the top of the spreadsheet, in which the user would enter the name lets say something like

Name, Position, Pay, Hire Date

Then they would press a button, or hit enter and the information put into the cells at the top would automatically be added to a list at the bottom.... Then be able to enter Another Set of data, and compile a large list this manner .... i am attaching pictures that may further clarify

**Richard Buttrey** · 01-23-2010, 07:37 PM

Hi,

Yes just name the cells A3:E3 say 'DataEntry' then in the Visual Basic Environment (Alt-F11) select your workbook in the top window on the left, from the menu choose Insert Module, and in the big code window on the right copy the macro below.


Sub AddData()
     Range("DataEntry").Copy Destination:= Range("A" & Application.Rows.Count).End(xlUp).Offset(1,0)
End Sub

you might prefer to have the new data entered at the top. In which case use


Sub AddData()
     Range("A5").EntireRow.Insert
     Range("DataEntry").Copy Destination:= Range("A5")
End Sub

Now on your worksheet add an icon, logo, picture, drawing object like say a rectangle etc, right click on the object, choose Assign Macro and select the 'AddData' macro.

HTH

**royUK** · 01-24-2010, 04:37 AM

If you have data in a table try this

1.Select a cell in the Table
2.From the data menu, click Form

**cporter5** · 01-24-2010, 08:50 PM

Thank you, i really like Richard Buttrey's idea, is there just a simple line of code i can add to that to have the entries placed on their own sheet ?

**Richard Buttrey** · 01-25-2010, 07:13 AM

Hi,

Yes.

Assuming the sheet which contains your staff data records is called say 'StaffData' then use the modified code below. I also missed the bit about clearing the data entry cells once the update has taken place so I've added that bit in too.

Sub AddData()
     Range("DataEntry").Copy Destination:=Sheets("Staffdata").Range("A" & Application.Rows.Count).End(xlUp).Offset(1, 0)
     Range("DataEntry").ClearContents
End Sub

..or at the top

Sub AddData()
     Range("A5").EntireRow.Insert
     Range("DataEntry").Copy Destination:=Sheets("StaffData").Range("A5")
     Range("DataEntry").ClearContents
End Sub

Regards