Copy Text Between Workbooks Not Working Right

[rodl66]

To give context, I have checkboxes in the worksheet, when a “NO” checkbox is ticked, it opens a message box to input text, which then copies that input text to the next empty cell B14-B22.

For this code, I have a button for each row 14-22, when I click it, it will copy the text from the row’s cell B to a different workbook’s next empty cell D (starting at D5). Text from B14 gets copied to D5 like it should; however, instead of copying text in B15 to D6, it copies B14 text again. This happens for B16, B17, etc.

Sub AddEntryToIRTrackerA()

Dim irTracker As Workbook
Dim was As Worksheet
Dim lastRow As Long
Dim newNumber As String
Dim entryType As String
Dim sourceText As String


'Set a reference to the "IR_Tracker" workbook
Set irTracker = Workbooks("IR_TRACKER.xlsm")

' Set a reference to the "MPF IR" worksheet in the "IR_Tracker" workbook
Set ws = irTracker.Sheets("MPF IR")

'Find the last used row in column A of the "MPF IR" worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Generate the new number
newNumber = "MPF-" & Year(Now) & "-" & Format(lastRow - 3, "0000")

'Add the new number to column A
ws.Cells(lastRow + 1, 1).Value = newNumber

'Add the current date to column B
ws.Cells(lastRow + 1, 2).Value = Format(Now, "yyyy-mm-dd")

'Add the entry type to column C
ws.Cells(lastRow + 1, 3).Value = "A"

'Get the text from column B of the active sheet in the "Wall Enclosures" workbook
sourceText = ThisWorkbook.Sheets("TYPE A").Cells(ActiveCell.Row, 2).Value

'Add the source text to column D in the MPF IR worksheet
ws.Cells(lastRow + 1, 4).Value = sourceText

End Sub

Thank you in advance.

 

[rlv01]

If the idea is to copy the Col B data for whatever row the ThisWorkbook.Sheets("TYPE A") button is on, then you have to find a way to communicate that row information to your Sub AddEntryToIRTrackerA.
sourceText = ThisWorkbook.Sheets("TYPE A").Cells(ActiveCell.Row, 2).Value won't work reliably because which worksheet ActiveCell.Row is referencing is uncertain.

One example

Sub AddEntryToIRTrackerA()
    Dim irTracker As Workbook, SaveWB As Workbook
    Dim ws As Worksheet, SaveWS As Worksheet
    Dim lastRow As Long
    Dim newNumber As String
    Dim sourceText As String
    Dim sourceRow As Long
    
    Set SaveWB = ActiveWorkbook
    Set SaveWS = ActiveSheet
    
    Application.ScreenUpdating = False
    With ThisWorkbook
        .Activate
        .Sheets("TYPE A").Activate
        sourceRow = ActiveCell.Row
    End With
    
    SaveWB.Activate
    SaveWS.Activate
    Application.ScreenUpdating = True
    
    'Set a reference to the "IR_Tracker" workbook
    Set irTracker = Workbooks("IR_TRACKER.xlsm")
    
    ' Set a reference to the "MPF IR" worksheet in the "IR_Tracker" workbook
    Set ws = irTracker.Sheets("MPF IR")
    
    'Find the last used row in column A of the "MPF IR" worksheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'Generate the new number
    newNumber = "MPF-" & Year(Now) & "-" & Format(lastRow - 3, "0000")
    
    'Add the new number to column A
    ws.Cells(lastRow + 1, 1).Value = newNumber
    
    'Add the current date to column B
    ws.Cells(lastRow + 1, 2).Value = Format(Now, "yyyy-mm-dd")
    
    'Add the entry type to column C
    ws.Cells(lastRow + 1, 3).Value = "A"
    
    'Get the text from column B of the active sheet in the "Wall Enclosures" workbook
    sourceText = ThisWorkbook.Sheets("TYPE A").Cells(sourceRow, 2).Value
    
    'Add the source text to column D in the MPF IR worksheet
    ws.Cells(lastRow + 1, 4).Value = sourceText
End Sub