If Range.Value = Yes then copy data to LOG RECORD - Records are overwriting

[bemp87]

Hi Community,

I have a VBA macro for a log record that copies information from a submission form and pastes it in a Log Record data file based on a IF condition. THe condition is partially working where if the condition is true then the data is copied to the log record, But when i try and process a second form it just overwrites the first submission on the log record instead of processing to the next row as it should and did prior to including the IF condition. Any guidance?

Code provided below:

Public Sub Update()

If Range("AH9").Value = "Yes" Then


Application.ScreenUpdating = False


ActiveSheet.Unprotect


Dim answer As Integer


Dim LUR As Long 'Last Used Row
LUR = Sheets("Data File").Range("A65000").End(xlUp).Offset(1, 0).Row


' This copies data from the Entry Form to the Log Record.


Const ms As Double = 0.000000011574


    Range("J9").Copy 'Name
    Sheets("Data File").Range("B" & LUR).PasteSpecial Paste:=xlPasteValues
    Application.Wait (Now + ms * 250)
'This saves the workbook after you hit the update button
ActiveWorkbook.Save




Application.ScreenUpdating = True
MsgBox "Your complaint case has been submitted."


On Error Resume Next


ElseIf Range("AH9").Value Is Nothing Then


On Error GoTo 0


End If


End Sub

 

[mumps]

Try:

Public Sub Update()
    If Range("AH9").Value = "Yes" Then
        Application.ScreenUpdating = False
        ActiveSheet.Unprotect
        Const ms As Double = 0.000000011574
        Range("J9").Copy 'Name
        Sheets("Data File").Cells(Sheets("Data File").Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Application.Wait (Now + ms * 250)
        'This saves the workbook after you hit the update button
        ActiveWorkbook.Save
        Application.ScreenUpdating = True
        MsgBox "Your complaint case has been submitted."
        On Error Resume Next
    ElseIf Range("AH9").Value Is Nothing Then
        On Error GoTo 0
    End If
End Sub

 

[DanteAmor]

with this instruction you are counting the rows according to column "A", but you are pasting the data in column "B"

LUR = Sheets("Data File").Range("[B][COLOR=#0000ff]A[/COLOR][/B]65000").End(xlUp).Offset(1, 0).Row

then you have to change the count to column "B"

LUR = Sheets("Data File").Range("[B][COLOR=#0000ff]B[/COLOR][/B]65000").End(xlUp).Offset(1, 0).Row

 

[Fluff]

The problem is that you are adding info to col B, but checking col A for the last row.
This

LUR = Sheets("Data File").Range("A65000").End(xlUp).Offset(1, 0).Row

should be

LUR = Sheets("Data File").Range("B"& rows.count).End(xlUp).Offset(1, 0).Row

You can also delete these three lines as they don't do anything

On Error Resume Next


ElseIf Range("AH9").Value Is Nothing Then


On Error GoTo 0

 

[DanteAmor]

If the active sheet is protected and you are only going to copy the data to paste in "Data File" sheet, then it is not necessary to unprotect sheet.
The code would look like this:

Public Sub Update()
    If Range("AH9").Value = "Yes" Then
        Application.ScreenUpdating = False
        Dim LUR As Long 'Last Used Row
        LUR = Sheets("Data File").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Range("J9").Copy 'Name
        Sheets("Data File").Range("B" & LUR).PasteSpecial Paste:=xlPasteValues
        ActiveWorkbook.Save
        Application.ScreenUpdating = True
        MsgBox "Your complaint case has been submitted."
    End If
End Sub