Transferring data using a macro button is overwriting existing data

AdamMc93

New Member
Joined
Nov 29, 2018
Messages
8
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.)

Code:
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.)

Code:
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.)

Code:
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 :)
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Adam

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

AdamMc93

New Member
Joined
Nov 29, 2018
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 29, 2018
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Aren't you using this code like this to determine which row to copy data to
Code:
With historyWks
    nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
 

AdamMc93

New Member
Joined
Nov 29, 2018
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
If you remove that 'functionality' how will you determine which row to put the data in?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,195
Messages
5,640,791
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top