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

jjacks60

New Member
Joined
Jul 28, 2014
Messages
40
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.

Rich (BB code):
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

Rich (BB code):
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top