Excel Macro - copy data to new row

Question

I've never used excel to do anything like this before, so could do with some advice.

I have a very simple sheet that has a basic form, when the user completes the form I want them to click a save button and the data from the form is then inserted into a new row.

Hopefully this image explains. Marcus's details have been added to the form, when SAVE is clicked I need to add a new row (11) with Marcus's details.

Is this possible ? Could someone point me In the right direction ?

This is the first time I've looked at macros and doing anything like this in excel.

Using the macro recorder I can copy data from C3:C5 & G3:G5 and paste them to row 11, but how do I add a new row and paste to that. Finally how do I bind the macro to the SAVE cell ?

Sub Copy()
'
' Copy Macro
'

'
    Range("C3:C5").Select
    Selection.Copy
    Range("A11").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("G3:G5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D11").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Answer 1

The Macro recorder in the bottom left corner is a good place to start (it's the form with the red circle overlaying on it):

If it's not there, then right click and select it (as below):

Then record what you want to happen automatically and that's you're starting point

Updated:

You need to create a save button, here's where you make a button and assign the copy macro to:

And here's you're updated code (see comments I've included which are after the "'" symbol:

Sub Copy()

    Range("C3:C5").Copy ' this replaces the select, then copy steps and is better syntax
    Range("A" & Range("A" & Cells.Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True ' The pastes relative to the last row (you code was an absolete referance to row 11 - hence it being overwritten)
    Range("G3:G5").Copy ' As per first comment
    Range("D" & Range("D" & Cells.Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True ' As per second comment
    Application.CutCopyMode = False ' escapes from copy/paste mode

End Sub

Answer 2

Try the below VBA procedure.

I ve written it based on form you presented in your question. The position of the fields are hard coded.

Public Sub SubmitForm()

Dim horizontalPosition As Integer
Dim formSheet As Worksheet

Set formSheet = ThisWorkbook.Worksheets(1)


horizontalPosition = Application.WorksheetFunction.CountA(formSheet.Range("A9:A1000")) + 9

formSheet.Cells(horizontalPosition, 1).Value = formSheet.Cells(3, 3).Value
formSheet.Cells(horizontalPosition, 2).Value = formSheet.Cells(4, 3).Value
formSheet.Cells(horizontalPosition, 3).Value = formSheet.Cells(5, 3).Value
formSheet.Cells(horizontalPosition, 4).Value = formSheet.Cells(3, 7).Value
formSheet.Cells(horizontalPosition, 5).Value = formSheet.Cells(4, 7).Value
formSheet.Cells(horizontalPosition, 6).Value = formSheet.Cells(5, 7).Value

End Sub

Answer 3

This will use an array to store and then print your info on the next row
(see the comments for more details!)

Arrays are much faster than referring to the sheet a lot

I also added the last part to clean your form inputs! (you can delete or comment it if you don't want it)

Public Sub Test_Tom()
    '''Define an array to contain your data
    Dim DatAa() As Variant
    ReDim DatAa(1 To 1, 1 To 6)

    '''Define the sheet you want to work on
    Dim wS As Worksheet
    Set wS = ThisWorkbook.ActiveSheet
    '''or
    'Set wS = ThisWorkbook.Sheets("Sheet's Name")

    '''Fill the data array
    DatAa(1, 1) = wS.Range("C3").Value
    DatAa(1, 2) = wS.Range("C4").Value
    DatAa(1, 3) = wS.Range("C5").Value
    DatAa(1, 4) = wS.Range("G3").Value
    DatAa(1, 5) = wS.Range("G4").Value
    DatAa(1, 6) = wS.Range("G5").Value

    '''Find the first available row
    Dim NextRow As Long
    NextRow = wS.Range("A" & wS.Rows.Count).End(xlUp).Row + 1

    '''Print your data in there!
    wS.Range("A" & NextRow).Resize(UBound(DatAa, 1), UBound(DatAa, 2)).Value = DatAa

    '''Clean your form
    wS.Range("C3").Value = vbNullString
    wS.Range("C4").Value = vbNullString
    wS.Range("C5").Value = vbNullString
    wS.Range("G3").Value = vbNullString
    wS.Range("G4").Value = vbNullString
    wS.Range("G5").Value = vbNullString
End Sub

Answer 4

You could tie it with your Worksheet_Change event, and check if someone changes the value in Cell "J6" (where you placed your "Save"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim PasteRow As Long, C As Range

If Not Intersect(Range("J6"), Target) Is Nothing Then '<-- check if the value in Cell J6 has cahnged, only then run the code below    
    PasteRow = Range("A9").End(xlDown).Row + 1 ' <-- find first empty row in Column A
    Set C = Range("A" & PasteRow) '<-- set the first cell, at column A

    C.Value = Range("C3").Value
    C.Offset(, 1).Value = Range("C4").Value
    C.Offset(, 2).Value = Range("C5").Value
    C.Offset(, 3).Value = Range("G3").Value
    C.Offset(, 4).Value = Range("G4").Value
    C.Offset(, 5).Value = Range("G5").Value

End If

End Sub

Answer 5

Try to bind this Macro to yout button. MacroToBindOnButton

Type employee
    Name As String
    Email As String
    Phone As String
    ID As String
    StaffNo As String
    Location As String
End Type

Sub MacroToBindOnButton()



' Create new Employee from sheet
Dim newEmployee As employee
newEmployee = createNewEmployee(newEmployee)

result = saveNewEmployee(newEmployee)

End Sub

Function createNewEmployee(employee As employee) As employee


employee.Name = ActiveWorkbook.activeSheet.Cells(2, 3).Value
employee.Email = ActiveWorkbook.activeSheet.Cells(3, 3).Value
employee.Phone = ActiveWorkbook.activeSheet.Cells(4, 3).Value
employee.ID = ActiveWorkbook.activeSheet.Cells(2, 7).Value
employee.StaffNo = ActiveWorkbook.activeSheet.Cells(3, 7).Value
employee.Location = ActiveWorkbook.activeSheet.Cells(4, 7).Value

createNewEmployee = employee
End Function

Function saveNewEmployee(newEmployee As employee)

Dim i As Integer
i = 9

Do While activeSheet.Cells(i, 1).Value <> ""

i = i + 1

Loop

' Save it into the rows
ActiveWorkbook.activeSheet.Cells(i, 1).Value = newEmployee.Name
 ActiveWorkbook.activeSheet.Cells(i, 2).Value = newEmployee.Email
 ActiveWorkbook.activeSheet.Cells(i, 3).Value = newEmployee.Phone
 ActiveWorkbook.activeSheet.Cells(i, 4).Value = newEmployee.ID
ActiveWorkbook.activeSheet.Cells(i, 5).Value = newEmployee.StaffNo
ActiveWorkbook.activeSheet.Cells(i, 6).Value = newEmployee.Location


   End Function