Copying simple form data into new databse Closed

Question

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

TrowaD · Answer

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").SelectRange("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("A2").ValueRange("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("B2").ValueRange("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Range("C2").ValueEnd 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

Copying simple form data into new databse

1 response

Similar discussions

Subscribe To Our Newsletter!