Help with macro for copy cell data from one worksheet to another in next available row.

[eages]

Hoping someone out there can help me. I am new to macros and do not have a clue where to start.

I have a worksheet called "Entry" and there are certain cells in which data is entered into. There is also a command button that when clicked I would like it to do the following:

1. Copy data that has been entered on the worksheet named "Entry" from certain cells such as C5, C7, I6 etc and paste them onto the next available row of a different worksheet named "Data".

2. I would also like it to sort the information by Column A in the "Data" worksheet and save. So that when the data on the "Entry" worksheet is cleared the information remains on the "Data" worksheet.

3. I would then like it to clear the certain cells on the "Entry" worksheet to allow new entries to be made.

Is this possible to do all 3 with just one click of the button and if so how to I ask it nicely to do this.

Thank you to all in advance for any assistance that you can give me.

 

[VoG]

Try something like this

Sub test()
With Sheets("Entry")
    .Range("C5").Copy Destination:=Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Range("C7").Copy Destination:=Sheets("Data").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .Range("I6").Copy Destination:=Sheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1)
    .Range("C5,C7,I6").ClearContents
End With
Sheets("Data").Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Save
End Sub

 

[jbeaucaire]

This should get you started:

Option Explicit

Sub AddToDatabase()
Dim wsData  As Worksheet:   Set wsData = Sheets("Data")
Dim wsEntry As Worksheet:   Set wsEntry = Sheets("Entry")
Dim NextRw  As Long

    With wsData
        NextRw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & NextRw) = wsEntry.Range("C5")
        .Range("B" & NextRw) = wsEntry.Range("C7")
        .Range("C" & NextRw) = wsEntry.Range("I6")
        
        .Range("A1").CurrentRegion.Sort .Range("A2"), xlAscending, Header:=xlGuess
    End With

wsEntry.Range("C5,C7,I6").Value = ""
Set wsData = Nothing
Set wsEntry = Nothing
End Sub

 

[eages]

Thanks for your quick reply VoG.

I know I am doing something that is basically stupid but it comes up with and error of
Compile error:
Expected End Sub

and has highlighted
Private Sub CommandButton2_Click()

do I take this out am I so unsure

 

[VoG]

If you want to assign my code to a button, first place the code in a standard module. Then add a button from the Forms toolbar to a worksheet and when prompted assign the macro test.

 

[eages]

Thank you VoG and also jbeaucaire you are gods!!!

 

[eages]

Back again, have just discovered a hiccup in which if data is not entered in "C5" of "Entry" worksheet how do I get the macro to either ignore the command to copy of all the relevant data that will populate that row into the "Data" worksheet or delete the data in a row in the "Data" worksheet where Column A is blank.

 

[VoG]

Try

Sub test()
With Sheets("Entry")
    If .Range("C5").Value = "" Then
        MsgBox "You must complete C5", vbExclamation
        Exit Sub
    End If
    .Range("C5").Copy Destination:=Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Range("C7").Copy Destination:=Sheets("Data").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .Range("I6").Copy Destination:=Sheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1)
    .Range("C5,C7,I6").ClearContents
End With
Sheets("Data").Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Save
End Sub

 

[eages]

thanks again VoG. You are saving my sanity a great deal