Copying simple form data into new databse [Closed]

Report
Posts
1
Registration date
Monday February 10, 2014
Status
Member
Last seen
February 10, 2014
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
Hello,

I'd like to create a simple form that I can then copy into a spreadsheet and will update another workbook for data. My form is very straightforward

A1= Name
B1= Location
C1= Date

So let's say the first form to come in is copied in and shows A2=Bob, B2=London, C2= 21/03/2014

I then have a spreadsheet set up on Sheet 2 with the same column headings and it copies the data from Sheet 1 where the form is pasted in to the relevant column entry in Sheet 2. I can do that bit.

However once I paste the next form in all the data in Sheet 2 will change and I will lose the ata form the first form. How do I ask excel to copy data from the form into thenext line on thespreadsheet? So if I have 50 forms I will have 50 rows of data? I'm not very good with macros if that's teh answer so please explain clearly as if I'm an idiot! Thank you :-)

Thanks,

Ros

1 reply

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Ros,

This is a very simple macro that simply copies the data from Sheet1 and paste it to the first available row in Sheet2.

Note that when not all three cells contain data, the first available rows are going to differ between columns.

Here is the code:
Sub RunMe()
Sheets("Sheet2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("A2").Value
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("B2").Value
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("C2").Value
End Sub

In case you don't know:
Hit Alt+F11 when Excel is active to launch the Microsoft Visual Basic window.
Goto top menu > insert > Module and paste the above code into the big white field.
You can close this window now, which returns you back to the Excel window.
Now hit Alt+F8, which displays all macro's. Now double-click RunMe to run the code/macro.

Let me know if I can help you further.

Best regards,
Trowa