Call a Macro from another WB - Run-time Error '1004'

[Rottimad]

Hi everyone,

I have written several macros to grab data from an external source and copy it to the open workbook but occasionally the exact same codes runs into the Run-time Error 1004.

The setup is I have around 20 Workbooks each which have the below code within them referring to a single data source workbook. Within this workbook is another Macro called 'SCorderInvData' which in turn grabs data from a 3rd source and carries out several calculations.

The below macro is supposed to open a workbook, run that workbooks macro, copy the resulting values and then close the workbook. When it runs (as I said it varies), it runs perfectly and forms one of several subs which each grab from a different workbook (all located within the same network/folder).

Sub PO_Data()

' Set workbook variables
Dim Target_Workbook As Workbook
Dim Source_Workbook As Workbook
Dim Target_Path As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Set workbook objects
Target_Path = "\\*****\*********\002 Commercial & Sub Contract\Commercial Pack Links\SC Orders Tables.xlsm"
Set Target_Workbook = Workbooks.Open(Target_Path)
Set Source_Workbook = ThisWorkbook

' Run macro within wb SC Orders Tables to update info in wb SC Orders Tables
[B]Application.Run ("'SC Orders Tables'!SCorderInvData")[/B]
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Set Last Column and Row Variables
Dim LR As Long, LC As Long
Dim LR1 As Long, LC1 As Long
Dim LR2 As Long, LC2 As Long
Dim LR3 As Long, LC3 As Long

'POs Raised Contract
LR = Target_Workbook.Sheets(4).Range("A1").End(xlDown).Row
LC = Target_Workbook.Sheets(4).Range("A1").End(xlToRight).Column

Target_Workbook.Sheets(4).Select
Range(Cells(1, 1), Cells(LR, LC)).Copy
Source_Workbook.Sheets("POs Raised Contract").Range("A1").PasteSpecial xlPasteFormats
Source_Workbook.Sheets("POs Raised Contract").Range("A1").PasteSpecial xlPasteValues

'Pymnts Made Contract
LR1 = Target_Workbook.Sheets(5).Range("A1").End(xlDown).Row
LC1 = Target_Workbook.Sheets(5).Range("A1").End(xlToRight).Column

Target_Workbook.Sheets("Pymnts made contract").Select
Range(Cells(1, 1), Cells(LR, LC)).Copy
Source_Workbook.Sheets("Pymnts Made Contract").Range("A1").PasteSpecial xlPasteFormats
Source_Workbook.Sheets("Pymnts Made Contract").Range("A1").PasteSpecial xlPasteValues

'POs Raised VOs
LR2 = Target_Workbook.Sheets(6).Range("A1").End(xlDown).Row
LC2 = Target_Workbook.Sheets(6).Range("A1").End(xlToRight).Column

Target_Workbook.Sheets(6).Select
Range(Cells(1, 1), Cells(LR, LC)).Copy
Source_Workbook.Sheets("Pos Raised VOs").Range("A1").PasteSpecial xlPasteFormats
Source_Workbook.Sheets("Pos Raised VOs").Range("A1").PasteSpecial xlPasteValues

'Pymnts Made VOs
LR3 = Target_Workbook.Sheets(7).Range("A1").End(xlDown).Row
LC3 = Target_Workbook.Sheets(7).Range("A1").End(xlToRight).Column

Target_Workbook.Sheets(7).Select
Range(Cells(1, 1), Cells(LR, LC)).Copy
Source_Workbook.Sheets("Pymnts Made VOs").Range("A1").PasteSpecial xlPasteFormats
Source_Workbook.Sheets("Pymnts Made VOs").Range("A1").PasteSpecial xlPasteValues

Target_Workbook.Save
Target_Workbook.Close

Sheets("Header").Activate

If Application.DisplayAlerts Then MsgBox "PO Data Updated"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

The section in bold is the culprit.

I know the problem I have is calling the macro from within the opened workbook. Ideally I want this to be idiot proof so that others can use a simple "one button does all", but this idiot can't get 100% success rate! If I simply remove this line and run the called macro myself manually in the source data I never encounter a problem. But given I need this to update every time the button is pressed that option isn't ideal.

Any help would be greatly appreciated.

 

[ranman256]

where in the code do you OPEN the workbook with the code?
workbooks.open "C\folder\SC Orders Tables.xls"

 

[Rottimad]

where in the code do you OPEN the workbook with the code?
workbooks.open "C\folder\SC Orders Tables.xls"

Set Target_Workbook = Workbooks.Open(Target_Path)

The workbook I am referring to is opened prior to my error occurring, so I know it's not opening the file I'm having problems with.

Edit - I also tried to call my macro by using the variable name

Application.Run (Target_Path & "!SCorderInvData")

Which encounters the same problem.

 

[Rottimad]

Does anyone have any thoughts on this?

I've had two uneventful days with no errors, this morning I do the same thing and its failed.

Ideally I don't want to add instructions to these WB's saying "If it breaks, I've no idea what I've done wrong. Just close all excel windows and then try again."

 

[Rottimad]

Re: Call a Macro from another WB - Run-time Error '1004' (HELP)

Had another appearance of the error which actually coincided with another issue which may shine some light on this (still escapes me how to solve it).

Situation:

I have 30 separate .xlsm files stored of a shared server. Every month I go into each one individually, run my updates and reports, save and close.

Today I opened a sheet ("Ham") did my work and closed it. Realised I wanted to change something and attempted to reopen "Ham" to be told someone else was using the sheet. I knew this not to be true but it still wouldn't allow me in. I had to reopen the sheet in read-only, close without saving, then it would allow me to open the full version.

Proceeding to the next sheet ("Bacon") I attempted to run my code and an error occurred:

" 'P:\Ham Folder\Ham Notes\Ham Budget\SC Orders Tables.xlsx' cannot be found. Check your spelling, or try a different path. "

But my code already opens the workbook to run it from?

' Set workbook objects
Target_Path = "\\company\Main Server\002 Commercial & Sub Contract\Commercial Pack Links\SC Orders Tables.xlsm"
Set Target_Workbook = Workbooks.Open(Target_Path)
Set Source_Workbook = ThisWorkbook

' Run macro within wb SC Orders Tables to update info in wb SC Orders Tables
Application.Run ("'SC Orders Tables'!SCorderInvData")

Does anyone have any suggestions on how to fix this???