Run code that updates databas ewhen file is saved

[GerardoLopez22]

So, I am fairly new to VBA and I am having a difficult time with this. I have a template that is to be modified and will be saved and create a new file for each modification (is a template for work shift information). For every different file saved, the main database needs to be updated, so every time it should insert the data in a new row.

NOTE: that only values from specific cells need to be updated on specific cells of the database.

Currently I have this code, but when I save it runs and pastes the same value for all the lines indicated in the loop. How can I make it stop after just one row and for it to know that on the next save it should continue in the following row?

Private Sub update_Database(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim lastRow As Long
    Dim wbData As Workbook
    Dim wsData As Worksheet
    Dim wsSource As Worksheet
    
    Set wsSource = ThisWorkbook.ActiveSheet
    Set wbData = Workbooks.Open("C:\Users\uia87497\Desktop\OEE Files\OEE_Machine_Shift\OEE_Calculation.xlsx")
    Set wsData = wbData.Sheets("OEE - Shift")
    
    For lastRow = 3 To 96
        With wsData
            .Cells(lastRow, 6).Value2 = wsSource.Range("H5").Value2
            .Cells(lastRow, 13).Value2 = wsSource.Range("P5").Value2
            .Cells(lastRow, 15).Value2 = wsSource.Range("D43").Value2
            .Cells(lastRow, 16).Value2 = wsSource.Range("J43").Value2
            .Cells(lastRow, 18).Value2 = wsSource.Range("S43").Value2
            .Cells(lastRow, 19).Value2 = wsSource.Range("P43").Value2
            .Cells(lastRow, 21).Value2 = wsSource.Range("V43").Value2
            .Cells(lastRow, 22).Value2 = wsSource.Range("G43").Value2
        End With
    Next lastRow
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub update_Database(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim lastRow As Long
    Dim wbData As Workbook
    Dim wsData As Worksheet
    Dim wsSource As Worksheet
    
    Set wsSource = ThisWorkbook.ActiveSheet
    Set wbData = Workbooks.Open("C:\Users\uia87497\Desktop\OEE Files\OEE_Machine_Shift\OEE_Calculation.xlsx")
    Set wsData = wbData.Sheets("OEE - Shift")
    
      With wsData
         lastRow = .Range("F" & Rows.Count).End(xlUp).Offset(1).Row
         .Cells(lastRow, 6).Value2 = wsSource.Range("H5").Value2
         .Cells(lastRow, 13).Value2 = wsSource.Range("P5").Value2
         .Cells(lastRow, 15).Value2 = wsSource.Range("D43").Value2
         .Cells(lastRow, 16).Value2 = wsSource.Range("J43").Value2
         .Cells(lastRow, 18).Value2 = wsSource.Range("S43").Value2
         .Cells(lastRow, 19).Value2 = wsSource.Range("P43").Value2
         .Cells(lastRow, 21).Value2 = wsSource.Range("V43").Value2
         .Cells(lastRow, 22).Value2 = wsSource.Range("G43").Value2
      End With
End Sub