User Form Transferring Data into Separate file

[durc09]

Hello, at work we have a shared spreadsheet which is updated by many different people.
Lots of people use this spreadsheet from around the site to enter new information. The information it holds is the movements of ISOs around site; where they are, what they have in them and how much. This is very fluid and can change daily - plant managers and planners need to know where our bulk material is and much we have stored in the ISOs

The spreadsheet is setup to transfer current data from the user form into the master data tab and then copy old information into an archive sheet within the same work book. What I would like to do is separate out the form and the datasheets - both master and archive.

Below is the code behind user form - what I need to know is how do I set it up to make it copy the same pieces of information but onto a different workbook saved elsewhere? In my head we would ideally have one spreadsheet that is the user form that the end users use to update records and then another spreadsheet that is used to keep the master data but also the archived older data.

The code as it is written now is below - I really appreciate anyone's help

Private Sub CloseForm_Click()
    Unload UserForm2
    shtMainScreen.Select
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

        If CloseMode = 0 Then Cancel = True
        Application.StatusBar = ""
        
End Sub
Private Sub UserForm_Initialize()
    With Me
        .boxContNum.RowSource = "rngContainers"
        .boxLocation.RowSource = "rngLocation"
        .boxStatus.RowSource = "rngStatus"
        .boxProduct.RowSource = "rngProduct"
        .boxResponsible.RowSource = "rngPlanner"
    End With
End Sub

Private Sub boxContNum_AfterUpdate()

        'Lookup values based on boxContNum
        On Error Resume Next
        With Me
        .boxCapacity = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 2, 0)
        .boxTareWeight = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 3, 0)
        .boxBaffled = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 4, 0)
        .boxDedicated = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 5, 0)
        .boxStatus = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 6, 0)
        .boxDateFilled = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 7, 0)
        .boxDateFilled.Value = Format(.boxDateFilled, "dd mmm yyyy")
        .boxLocation = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 8, 0)
        .boxProduct = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 9, 0)
        .boxBatchNumber = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 10, 0)
        .boxNetQty = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 11, 0)
        .boxDateEmptied = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 12, 0)
        .boxDateEmptied.Value = Format(.boxDateEmptied, "dd mmm yyyy")
        .boxPreviousProduct = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 13, 0)
        .boxResponsible = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 14, 0)
        .boxInsp1 = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 15, 0)
        .boxInsp1.Value = Format(.boxInsp1, "dd-mmm-yy")
        .boxInsp1.BackColor = getColor(DateDiff("d", Now(), .boxInsp1))
        .boxInsp1.Value = Format(.boxInsp1, "mmm-yy")
        .boxInsp2 = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 16, 0)
        .boxInsp2.Value = Format(.boxInsp2, "dd-mmm-yy")
        .boxInsp2.BackColor = getColor(DateDiff("d", Now(), .boxInsp2))
        .boxInsp2.Value = Format(.boxInsp2, "mmm-yy")
        .boxInsp3 = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 17, 0)
        .boxInsp3.Value = Format(.boxInsp3, "dd-mmm-yy")
        .boxInsp3.BackColor = getColor(DateDiff("d", Now(), .boxInsp3))
        .boxInsp3.Value = Format(.boxInsp3, "mmm-yy")
        .boxBottomAccess = Application.WorksheetFunction.VLookup(.boxContNum, shtContainers.Range("tblContainers"), 18, 0)
        End With
        
End Sub
Private Function getColor(days)
    Select Case days
        Case Is <= 0
            getColor = vbRed
        Case 1 To 90
            getColor = RGB(255, 191, 0)
        Case Else
            getColor = vbGreen
    End Select
End Function


Private Sub UpdateRecord_Click()

    
    Application.ScreenUpdating = False
    
               
    shtContainers.Unprotect Password:="manlog"
    shtHistory.Unprotect Password:="manlog"
        
    If Me.boxContNum.Value = "" Then
        MsgBox "Container Number Can Not be Blank!", vbExclamation, "Container Number"
    Exit Sub
    End If
    
    If Me.boxStatus.Value = "" Then
        MsgBox "Status Can Not be Blank!", vbExclamation, "Container Number"
    Exit Sub
    End If
    
    If Me.boxDateFilled.Value = "" Then
        MsgBox "Date Filled Can Not be Blank!", vbExclamation, "Container Number"
    Exit Sub
    End If
    
    If Me.boxNetQty.Value = "" Then
        MsgBox "Net Qty Can Not be Blank!", vbExclamation, "Container Number"
    Exit Sub
    End If
    
    If Me.boxResponsible.Value = "" Then
        MsgBox "Responsible Person Can Not be Blank!", vbExclamation, "Container Number"
    Exit Sub
    End If
    
    shtContainers.Select
    
    Dim rowselect As Double
    Dim findrow As Range
    Dim lastRowHistory As Long
    
    Set findrow = shtContainers.Range("rngcontainers").Find(what:=Me.boxContNum.Value, LookIn:=xlValues)
    
    rowselect = findrow.Row
    
    'move current record to history

    lastRowHistory = shtHistory.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowHistory = lastRowHistory + 1

    Rows(rowselect).Select
    Selection.Copy
    shtHistory.Select
    Rows(lastRowHistory).Select
    ActiveSheet.Paste
    shtContainers.Select
    
    On Error Resume Next
    Cells(rowselect, 13) = Cells(rowselect, 9)
    Cells(rowselect, 2) = Me.boxCapacity.Text
    Cells(rowselect, 3) = Me.boxTareWeight.Text
    Cells(rowselect, 4) = Me.boxBaffled.Text
    Cells(rowselect, 5) = Me.boxDedicated.Text
    Cells(rowselect, 6) = Me.boxStatus.Text
    Cells(rowselect, 7) = CDate(Me.boxDateFilled.Text)
    Cells(rowselect, 8) = Me.boxLocation.Text
    Cells(rowselect, 9) = Me.boxProduct.Text
    Cells(rowselect, 10) = Me.boxBatchNumber.Text
    Cells(rowselect, 11) = Me.boxNetQty.Text
    Cells(rowselect, 12) = CDate(Me.boxDateEmptied.Text)
    Cells(rowselect, 14) = Me.boxResponsible.Text
    Cells(rowselect, 19) = Now
    Cells(rowselect, 20) = VBA.Environ("Username")

    Application.StatusBar = "Record Updated!"

    shtContainers.Protect Password:="manlog"
    shtContainers.Protect AllowFiltering:=True
    shtHistory.Protect Password:="manlog"
    shtHistory.Protect AllowFiltering:=True
    

    

End Sub