How to: copy data from sheet 1 to 2 after hitting a submit button?

**WillGe** · 01-30-2012, 06:11 AM

I've got a file attached of the kind of thing I'm after.... two sheets: data from the first should transfer to the second when the user presses the SUBMIT button, so the second sheet is all plain data with each new entry coming in beneath the previous row. If (hopefully, when) this one is up and running I'd like to add a few other fields too.

The highlighted fields should have drop down menus, with options as described. The date should be in a specific format, say DD/MM/YYYY - the format must be the same for each entry - can it be set to DD/MM/YYYY so if someone enters the date in another format (e.g. 4 April 12) it will convert it? Or is it easier to have a calendar pop-up so you can just mouse click the date?

Once all fields are full, the user will click the SUBMIT button and all the entries should go the the second sheet.

How do I do this??? I'm sure it's not the most complicated thing you could hear of but it's certainly well over my head, I don't know where to start. Please help

!!

Will

**Kyle123** · 01-30-2012, 06:54 AM

something like:

Sub movedata()
    With Sheet2.Cells(1, 1).CurrentRegion.Offset(1)
        .ClearContents
        .Resize(Sheet1.Cells(3, 1).CurrentRegion.Offset(2).Rows.Count).Value = _
                                    Sheet1.Cells(3, 1).CurrentRegion.Offset(2).Value
    End With
End Sub

You can set data validation on column A to ensure only valid dates are entered

**WillGe** · 02-06-2012, 03:29 AM

Thanks for the code, it's working well except for two things... when the data is sent to sheet 2, the input cells in sheet 1 don't clear; also the data that goes to sheet 2 is overwriting itself on the first row rather than shifting down a row each time new data comes across - is there a way to fix this? I would have thought the third code line ".ClearContents" would deal with the first prob? and maybe the "Offset" part of the rest would take care of the second? Along those lines can you please list in plain language what the code is asking for each different part? That would be really interesting/useful. Thanks for the help

- Will

**Kyle123** · 02-06-2012, 04:22 AM

OK I understand, the above was for if you are adding more rows on the first sheet and want them all moving across, but you are wanting to use the same cells to add rows.

Try this:

Sub movedata()
    With Sheet2.Cells(1, 1).CurrentRegion
         .Offset(.Rows.Count, 0).Resize(1).Value = Sheet1.Range("A3:J3").Value
         Sheet1.Range("A3:J3").ClearContents
    End With
End Sub

**WillGe** · 02-06-2012, 06:28 AM

Unreal, works a treat! Thanks heaps for helping out. I'll get to work prettying it up.

**WillGe** · 02-12-2012, 12:29 AM

Hi, I'm having some troubles linking the code to a command button in excel 2003 (the attached file is in excel 2011 for mac). I need it work on a work computer using excel 2003. I've attached the new spreadsheet, it has both an autoshape submit button, which works ok, and and a command button - which I like the look of more but can't get to work...... Can you please have a look at the word doc attached, I've got some screen shots of the process I'm using to attach the code to the command button, I'm doing something wrong somewhere along the line.

Another thing, an added feature I'd like to have is, for example, if someone clicks on the "exam type" list and selects "orthopaedics" then I would like a designated list to selectable under the next cell, "exam" - so different lists will appear under "exam" depending on what is selected in "exam type". Is this possible?

Thanks heaps!

doserepor.xls problems.docx