Macro stops after opening external workbook - No errors though...

[jjacks60]

I've made a macro to open an external workbook, copy the data on sheet 1, and then paste to A1 in a raw data tab of another workbook.

Sub CpyProdSch()
    Dim wbk As Workbook
    Dim wsFc As Worksheet
    Dim strFileName As String
    Dim strFilePath As String
    Dim bResponse As Boolean
    Dim MsgBoxResult As Long
    
    MsgBoxResult = MsgBox("You must be connected to the Clorox network to run", vbOKCancel, "Warning")
        If MsgBoxResult = vbCancel Then
        Exit Sub
        ElseIf MsgBoxResult = vbOK Then
    
    Set wsFc = Workbooks("Raw Data Production").Worksheets("Raw Data")
    strFilePath = "\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"  ' this could also be a constant
    strFileName = Dir(strFilePath)
    
    'On Error Goto
    
    'bResponse = Application.Dialogs(xlDialogOpen.Show( _
    '"\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"))
    'If Not bResponse Then Exit Sub
    'Else
    
    Set wbk = Workbooks.Open(strFilePath & strFileName, False, True)
    
     wbk.Worksheets(1).Range("A1:BM500").Select _
    .Copy
    Windows("Raw Data pRODUCTION.xlsx").Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End If
    wbk.Close True
    Set wbk = Nothing
    
    Workbooks("Raw Data Production").Worksheets("Raw Data").Activate
    MsgBox "Please Click Step 2", vbOKOnly, "Step 1 is Complete"
    
End Sub

Set wbk = Workbooks.Open(strFilePath & strFileName, False, True)

This is where it stops every time. The workbook will open, and it just stops on the first sheet performing no action and creating no error message.
The workbook is read only, but I've gotten the macro to work before - when I was editing it today it stopped working and I can't figure out why.

Any help is greatly appreciated as I'm approaching a deadline.

 

[jjacks60]

I've made a macro to open an external workbook, copy the data on sheet 1, and then paste to A1 in a raw data tab of another workbook.

Sub CpyProdSch()
    Dim wbk As Workbook
    Dim wsFc As Worksheet
    Dim strFileName As String
    Dim strFilePath As String
    Dim bResponse As Boolean
    Dim MsgBoxResult As Long
    
    MsgBoxResult = MsgBox("You must be connected to the Clorox network to run", vbOKCancel, "Warning")
        If MsgBoxResult = vbCancel Then
        Exit Sub
        ElseIf MsgBoxResult = vbOK Then
    
    Set wsFc = Workbooks("Raw Data Production").Worksheets("Raw Data")
    strFilePath = "\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"  ' this could also be a constant
    strFileName = Dir(strFilePath)
    
    'On Error Goto
    
    'bResponse = Application.Dialogs(xlDialogOpen.Show( _
    '"\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"))
    'If Not bResponse Then Exit Sub
    'Else
    
    Set wbk = Workbooks.Open(strFilePath & strFileName, False, True)
    
     wbk.Worksheets(1).Range("A1:BM500").Select _
    .Copy
    Windows("Raw Data pRODUCTION.xlsx").Activate
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End If
    wbk.Close True
    Set wbk = Nothing
    
    Workbooks("Raw Data Production").Worksheets("Raw Data").Activate
    MsgBox "Please Click Step 2", vbOKOnly, "Step 1 is Complete"
    
End Sub

Set wbk = Workbooks.Open(strFilePath & strFileName, False, True)

This is where it stops every time. The workbook will open, and it just stops on the first sheet performing no action and creating no error message.
The workbook is read only, but I've gotten the macro to work before - when I was editing it today it stopped working and I can't figure out why.

Any help is greatly appreciated as I'm approaching a deadline.

To clarify - the reason I altered the code was to only copy visible cells (many rows are hidden that aren't operating).

 

[ProfessorPorcupine]

Hey, did you ever figure this problem out? I am having the exact same problem.

Thanks,

 

[ProfessorPorcupine]

I think I fixed my similar problem by adding DoEvents right before I open the workbook. I think this allows the Operating System to take care of some things that may have been causing the problem? At any rate, all's well that ends well.