Transferring data using a macro button is overwriting existing data

[AdamMc93]

Hi there,

I have created a spreadsheet with three front-end forms, which are designed to transfer data across to three corresponding worksheets. This functionality works successfully, however i also require the user to be able to enter data using the worksheet if they wish.

The issue now, is that when data is entered directly into the worksheet, and then the user uses the form say for example, for the next row of data; it won't populate the next row, it will instead overwrite what the user typed in the worksheet.

Does anyone know of a way that the form will populate data in the row below the data that was entered in the worksheet, so as not to lose or overwrite any data?

The three macro button VBA's are as follows:

1.)

Sub UpdateLeadWorksheet()

    'cells to copy from Input sheet - some contain formulas
    myCopy = "C7:C15,C18:C23,G7:G14,G16:G24,K7:K12,K14:K19"

    Set inputWks = Worksheets("Lead Input Form")
    Set historyWks = Worksheets("BASFLead")

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

    With inputWks
        Set myRng = .Range(myCopy)
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            If oCol = 11 Then oCol = 14 Else oCol = oCol + 1
        Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

2.)

Sub CustomerRecords()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    'cells to copy from Input sheet - some contain formulas
    myCopy = "C7,C8,C9,C10,C11,C12,F7,F8,F9,F10,F11,F12,F13,F14,F15,I7,I8,I9,I10,I11,I12,I13,I14,I15,I16"

    Set inputWks = Worksheets("CustomerRecords Input Form")
    Set historyWks = Worksheets("BASFCustomerRecords")

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

    With inputWks
        Set myRng = .Range(myCopy)
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            If oCol = 8 Then oCol = 13 Else oCol = oCol + 1
        Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

3.)

Sub SalesRepActivity()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    'cells to copy from Input sheet - some contain formulas
    myCopy = "C7,C8,C9,C10,C11,C14,C15,C16,C17,C18,C19,C20,C21"

    Set inputWks = Worksheets("SalesRepActivity Input Form")
    Set historyWks = Worksheets("BASFSalesRepActivityRecord")

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

    With inputWks
        Set myRng = .Range(myCopy)
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            If oCol = 7 Then oCol = 10 Else oCol = oCol + 1
        Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

If anyone is able to help with my query it would be very much appreciated.

Many thanks,

Adam

 

[Norie]

Adam

When the user enters data manually are they entering anything in column A?

 

[AdamMc93]

Hi,

Thank you for your response. No, columns A and B are left blank if the user manually enters anything - those columns only come into play if the user has used the form.

Thanks

 

[Norie]

In the code you posted you appear to be using column A to determine which row to copy data to.

If that column is blank then you will end up copying the data to the wrong row(s).

You either need to change the manual process so that column A is populated when data is entered or, in the code, use another column to determine the row to copy data.

 

[AdamMc93]

Hi,

The functionality included in columns A and B is not essential, so this can be removed form the macro code if that makes the overall spreadsheet work as it should.

Any idea what the new VBA code would look like / include?

 

[Norie]

Aren't you using this code like this to determine which row to copy data to

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

 

[AdamMc93]

Hi,

I have attempted to fix this by changing the column to "C", so as to avoid the potentially blank A and B. However this then transfers the data down at line 500. Any idea of a fix? Like i said, I can remove the macro A and B functionality completely if need be.

Thanks for your help

 

[Norie]

If you remove that 'functionality' how will you determine which row to put the data in?