Copying data from a data entry form to specific cells on another sheet based on 'category' without overwriting existing data

[cteooo]

Hello,

I've made a data entry form and would like to automate the entry process using VBA when I click 'Submit'. I would like to copy values from the entry form to certain cells/columns to another sheet (it is a master data log) with existing data without overwriting them. However, it gets a little complicated as I have multiple master log sheets for different groups of people which is dependent on the category.

I have a code ready but it overwrites the existing data in the respective master data logs. The transfer of data must start below any existing data that is there. I would appreciate any guidance on how to tweak it to prevent this. Below is the code and a screenshot of my form and a sample master log.

Thank you so much!!

Option Explicit

Sub Reset_Form()

    Dim iMessage As VbMsgBoxResult
  
    iMessage = MsgBox("Do you want to reset this form?", vbYesNo + vbQuestion, "Reset Confirmation")

    If iMessage = vbNo Then Exit Sub
  
    ThisWorkbook.Sheets("Form").Range("I10,I12,I14,I16,I18,I20,I22,I24,I26,I28,I32,I36,I38,I40,I44,I46,I48,I50,I54,I56,I58,I60,I64,I66,I68,I70,I72,I74,I78,I80,I82,I84,I88").Value = ""
  

End Sub

Sub Submit_Details()

    Dim shCategory As Worksheet
    Dim shForm As Worksheet
  
    Dim iCurrentRow As Integer
  
    Dim sCategoryName As String
  
    Set shForm = ThisWorkbook.Sheets("Form")
  
    sCategoryName = shForm.Range("I18").Value
  
    Set shCategory = ThisWorkbook.Sheets(sCategoryName)
  
    iCurrentRow = shCategory.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
  
    With shCategory
  
      
      
        .Cells(iCurrentRow, 2) = shForm.Range("I10")
      
        .Cells(iCurrentRow, 3) = shForm.Range("I12")
      
        .Cells(iCurrentRow, 4) = shForm.Range("I14")
      
        .Cells(iCurrentRow, 6) = shForm.Range("I16")
      
        .Cells(iCurrentRow, 7) = shForm.Range("I18")
      
        .Cells(iCurrentRow, 8) = shForm.Range("I20")
      
        .Cells(iCurrentRow, 9) = shForm.Range("I22")
      
        .Cells(iCurrentRow, 10) = shForm.Range("I24")
      
        .Cells(iCurrentRow, 11) = shForm.Range("I26")
      
        .Cells(iCurrentRow, 12) = shForm.Range("I28")
      
        .Cells(iCurrentRow, 13) = shForm.Range("I32")
      
        .Cells(iCurrentRow, 14) = shForm.Range("I36")
      
        .Cells(iCurrentRow, 15) = shForm.Range("I38")
      
        .Cells(iCurrentRow, 16) = shForm.Range("I40")
      
        .Cells(iCurrentRow, 17) = shForm.Range("I44")
      
        .Cells(iCurrentRow, 18) = shForm.Range("I46")
      
        .Cells(iCurrentRow, 19) = shForm.Range("I48")
      
        .Cells(iCurrentRow, 20) = shForm.Range("I50")
      
        .Cells(iCurrentRow, 21) = shForm.Range("I54")
      
        .Cells(iCurrentRow, 22) = shForm.Range("I56")
      
        .Cells(iCurrentRow, 23) = shForm.Range("I58")
      
        .Cells(iCurrentRow, 24) = shForm.Range("II60")
      
        .Cells(iCurrentRow, 25) = shForm.Range("I64")
      
        .Cells(iCurrentRow, 26) = shForm.Range("I66")
      
        .Cells(iCurrentRow, 27) = shForm.Range("I68")
      
        .Cells(iCurrentRow, 28) = shForm.Range("I70")
      
        .Cells(iCurrentRow, 29) = shForm.Range("I72")
      
        .Cells(iCurrentRow, 30) = shForm.Range("I74")
      
        .Cells(iCurrentRow, 31) = shForm.Range("I78")
      
        .Cells(iCurrentRow, 32) = shForm.Range("I80")
             
        .Cells(iCurrentRow, 37) = shForm.Range("I82")
      
        .Cells(iCurrentRow, 40) = shForm.Range("I84")
      
        .Cells(iCurrentRow, 41) = shForm.Range("I88")
      
  
    End With
  
    shForm.Range("I10,I12,I14,I16,I18,I20,I22,I24,I26,I28,I32,I36,I38,I40,I44,I46,I48,I50,I54,I56,I58,I60,I64,I66,I68,I70,I72,I74,I78,I80,I82,I84,I88").Value = ""
  
    MsgBox "Data submitted successfully!"
  


End Sub

 

[SQUIDD]

Hi

try changing this from

iCurrentRow = shCategory.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

to

iCurrentRow = shCategory.Range("B" & Application.Rows.Count).End(xlUp).Row + 1

Because I can only see you inputing new data into column B?

dave

 

[cteooo]

Hi

try changing this from

iCurrentRow = shCategory.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

to

iCurrentRow = shCategory.Range("B" & Application.Rows.Count).End(xlUp).Row + 1

Because I can only see you inputing new data into column B?

dave

Hi Dave,

Thank you so much for the tip! It works great now!

 

[SQUIDD]

Your welcome.

Thanks for the feedback.