Excel Macro to copy to a data sheet then clear the copied

[CNS_PEOPLE]

I am working on a excel sheet, where I have a 4 cells that i enter information into. I am trying to make a button inside this sheet that has a macro assigned to it. The button needs to copy the cells (E3:E6) in the column (E) once it has copied the data from these cells paste them to another Sheet (Data). Once that done I need it to clear (E3:E6). But I also need this to work when i have more than one. I can record a macro to do most of this but I get stuck when i want Sheet (Data) to have more then one logged in it. I am trying to make Sheet (Data) a log sheet where it keeps track of all the information that you entered and then clicked the Add button.

I am having issues with making the code move to the next row once data has been entered in the row above it.

If someone can please assist or help with this it would be helpful and appreciated.

 

[CNS_PEOPLE]

Using the code below with names I have figured out how to do this.



Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet


Dim nextRow As Long
Dim oCol As Long


Dim myCopy As Range
Dim myTest As Range

Dim lRsp As Long


Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Data History")
oCol = 3 'order info is pasted on data sheet, starting in this column

'check for duplicate order ID in database
If inputWks.Range("CheckID") = True Then
lRsp = MsgBox("Order ID already in database. Update record?", vbQuestion + vbYesNo, "Duplicate ID")
If lRsp = vbYes Then
UpdateLogRecord
Else
MsgBox "Please change ID Number to a unique number."
End If

Else

'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("DataEntry")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
'mandatory fields are tested in hidden column
Set myTest = myCopy.Offset(0, 2)

If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
'enter date and time stamp in record
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "dd/mmm/yyyy"
End With
'enter user name in column B
.Cells(nextRow, "B").Value = Application.UserName
'copy the order data and paste onto data sheet
myCopy.Copy
.Cells(nextRow, oCol).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With

'clear input cells that contain constants
Clear
End If


End Sub
Sub UpdateLogRecord()


Dim historyWks As Worksheet
Dim inputWks As Worksheet


Dim lRec As Long
Dim oCol As Long
Dim lRecRow As Long


Dim myCopy As Range
Dim myTest As Range

Dim lRsp As Long

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Data History")
oCol = 5 'order info is pasted on data sheet, starting in this column

'check for duplicate order ID in database
If inputWks.Range("CheckID") = False Then
lRsp = MsgBox("ID Number not in database. Add record?", vbQuestion + vbYesNo, "New Order ID")
If lRsp = vbYes Then
UpdateLogWorksheet
Else
MsgBox "Please select ID Number that is in the database."
End If

Else

'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("DataEntry")

lRec = inputWks.Range("CurrRec").Value
lRecRow = lRec + 1

With inputWks
Set myTest = myCopy.Offset(0, 2)

If Application.Count(myTest) > 0 Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(lRecRow, "A")
.Value = Now
.NumberFormat = "dd/mmm/yyyy"
End With
.Cells(lRecRow, "B").Value = Application.UserName

myCopy.Copy
.Cells(lRecRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End With

'clear input cells that contain constants
Clear
End If

End Sub