User form data entry into two workbooks

[davidkhbgh]

I need data from a userform in workbook A to enter into both workbook A and workbook B. This code works for workbook A but does not populate data into workbook B. What have I got wrong?

Private Sub btnEnterProductionData_Click()

    Dim dteStartDate As Date
    Dim strCalender As String
    Dim strShift As String
    Dim dblGTNum As Double
    Dim dblItemNum As Double
    Dim strCustOrderNum As String
    Dim dteStartTime As Date
    Dim strOperator As String
    Dim strCoord As String
    dteStartDate = CustomerData.DateEntry.Value
    strCalender = CustomerData.CalenderList.Value
    strShift = CustomerData.ShiftList.Value
    dblGTNum = CustomerData.OrderNumber.Value
    dblItemNum = CustomerData.ItemNumber.Value
    strCustOrderNum = CustomerData.CustPONum.Value
    dteStartTime = CustomerData.TimeEntry.Value
    strOperator = CustomerData.OperatorList.Value
    strCoord = CustomerData.CoordinatorList.Value
    
            'check for calender entry
    If Trim(Me.CalenderList.Value) = "" Then
        Me.CalenderList.SetFocus
        MsgBox "Please enter Calender", vbCritical, "Calender"
    Exit Sub
    End If
        'check for coordinator entry
    If Trim(Me.CoordinatorList.Value) = "" Then
        Me.CoordinatorList.SetFocus
        MsgBox "Please enter first floor Shift Coordinator", vbCritical, "Shift Coordinator"
    Exit Sub
    End If
        'check for operator entry
    If Trim(Me.OperatorList.Value) = "" Then
        Me.OperatorList.SetFocus
        MsgBox "Please enter Calender Operator", vbCritical, "Calender Operator"
    Exit Sub
    End If
        'check for shift entry
    If Trim(Me.ShiftList.Value) = "" Then
        Me.ShiftList.SetFocus
        MsgBox "Please enter shift", vbCritical, "Shift"
    Exit Sub
    End If
        'check for order number entry
    If Trim(Me.OrderNumber.Value) = "" Then
        Me.OrderNumber.SetFocus
        MsgBox "Please enter order number", vbCritical, "Order Number"
    Exit Sub
    End If
        'check order number for length
    If Len(OrderNumber.Value) <> 5 Then
        Me.OrderNumber.SetFocus
        MsgBox "Confirm order number is 5 digits", vbCritical, "Order Number"
    Exit Sub
    End If
            'check for item number entry
        If Trim(Me.ItemNumber.Value) = "" Then
        Me.ItemNumber.SetFocus
        MsgBox "Please enter item number", vbCritical, "Item Number"
    Exit Sub
    End If
            'check for item number length
    If Len(ItemNumber.Value) <> 6 And Len(ItemNumber.Value) <> 7 Then
        Me.ItemNumber.SetFocus
        MsgBox "Confirm item number is 6 digits or 6 digits followed by one letter", vbCritical, "Item Number"
    Exit Sub
    End If
  
    Windows("Tally Sheet ver7.2.xls").Activate
        ActiveWorkbook.Sheets("TallySheet").Activate
            'set start time
            Sheet1.Range("C6").Value = dteStartTime
            'set item number
            Sheet1.Range("C4").Value = dblItemNum
            'set order number
            Sheet1.Range("C3").Value = dblGTNum
            'set shift
            Sheet1.Range("E3").Value = strShift
            'set Customer PO Number
            Sheet3.Range("D12").Value = strCustOrderNum
            'set coordinator
            Sheet1.Range("E5").Value = strCoord
            'set operator
            Sheet1.Range("C5").Value = strOperator
            'set date
            Sheet1.Range("E2").Value = dteStartDate

            '**************************************************
            
    Windows("Tally Sheet Master.xls").Activate
        ActiveWorkbook.Sheets("ProductionRecord").Select
            'set start time
            Sheet1.Range("C6").Value = dteStartTime
            'set item number
            Sheet1.Range("C4").Value = dblItemNum
            'set order number
            Sheet1.Range("C3").Value = dblGTNum
            'set shift
            Sheet1.Range("E3").Value = strShift
            'set Customer PO Number
            Sheet3.Range("D12").Value = strCustOrderNum
            'set coordinator
            Sheet1.Range("E5").Value = strCoord
            'set operator
            Sheet1.Range("C5").Value = strOperator
            'set date
            Sheet1.Range("E2").Value = dteStartDate
        
        '**************************************************
    Unload Me

Thanks for the help.

Dave

 

[hayden]

Windows("Tally Sheet ver7.2.xls").Activate
ActiveWorkbook.Sheets("TallySheet").Activate

That is the frist one.

The second one is this

Windows("Tally Sheet Master.xls").Activate
ActiveWorkbook.Sheets("ProductionRecord").Select

Maybe change it from .slect to .activate and see if that helps.

I am pretty new to VBA so if it doesn't help let us know and I am sure someone else can help you out.

Hayden

 

[davidkhbgh]

I tried both select and activate and both worked in the same manner. No data in workbook B.

Dave

 

[hayden]

Hummm,
Ok what about this:

Workbooks("Tally Sheet Master.xls").Sheets("ProductionRecord").Range("C6").Value = dteStartTime

Only change all of them to that. I would step through it wtih f8 and see if you can figure out where specifically it is breaking.

Hayden

(Oh and by the way it might just be workbook and not workbooks I don't remember off the top of my head).

 

[davidkhbgh]

That worked. Thanks, Dave