Remote Sub Program Execution

[Mechanic]

Please give me an example of the command I would use to execute a sub program in a separate open workbook.

 

[Ahnold]

I believe this is what you are looking for:

Sub test()
    Application.Run "Book5!test1"
End Sub

 

[Mechanic]

This worked, however it ran the macro stored in the child and put the results in the parent (calling workbook) workbook. I wanted the child workbook to run data stored in the child and place the results in the child leaving the parent untouched. How do I do that?

Mechanic

 

[Ahnold]

Without knowing your code, I don't know specifically what to do, but I will give you an example.

If you are accessing the code from "Book2" use the same Application.Run method. If the code you want to run is in "Book1" and you want it to run in "Book1" and in "Sheet1" and as an example the first step in the code you are trying to run is Range("A1").select you could change it to Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1").select

 

[just_jon]

This worked, however it ran the macro stored in the child and put the results in the parent (calling workbook) workbook. I wanted the child workbook to run data stored in the child and place the results in the child leaving the parent untouched. How do I do that?

Mechanic

In the child workbook's macro, perhaps prefix your references with ThisWorkbook. or use --

With ThisWorkbook
' do stuff
End Wth

 

[Mechanic]

I tried Ahnold's suggestion and it works. Unfortunately, it will be code intensive for all the references I have. Just_jon's suggestions seem like what I need but unfortunately I couldn't get the first one to work. Could you please offer some code so I can see if I'm doing it right. The second suggestion:

With ThisWorkbook
'do stuff
End With

is the most applicable to my work but it still ran in the calling (parent) workbook. For 'do stuff, I ran:

Range("A1").value=Range("A1").value+1

I see what the intent is, but it ran in the parent workbook, perhaps I used it incorrectly. Is there something else I should do to make it work?

Please forgive my rookie skills but, I', basically self taught.

I appreciate everyones help. Thank you,
Mechanic

 

[just_jon]

I tried Ahnold's suggestion and it works. Unfortunately, it will be code intensive for all the references I have. Just_jon's suggestions seem like what I need but unfortunately I couldn't get the first one to work. Could you please offer some code so I can see if I'm doing it right. The second suggestion:

With ThisWorkbook
'do stuff
End With

is the most applicable to my work but it still ran in the calling (parent) workbook. For 'do stuff, I ran:

Range("A1").value=Range("A1").value+1
.
I see what the intent is, but it ran in the parent workbook, perhaps I used it incorrectly. Is there something else I should do to make it work?

Please forgive my rookie skills but, I', basically self taught.

I appreciate everyones help. Thank you,
Mechanic

In a With block, reference ranges/cells prefixed with a "." as in --

With ThisWorkbook
.Range("A1")=.Range("A1")+1
End With

BTW, no need for the .value bit -- that's the default if nothing specified.

Does this help?

 

[Mechanic]

I tried:

With ThisWorkbook
.Range("A1")=.Range("A1") +1
End With

and got the following error:

Complie Error:
Method or data member not found

Did I miss something?
Mechanic

 

[just_jon]

Sorry --

With ThisWorkbook
.Worksheets("sheethere").Range("A1")=...

or

With ThisWorkbook
With Worksheets("sheethere")
.Range("A1")=...

or

With ThisWorkbook.Worksheets("sheethere")
.Range("A1")=...


Need to have a sheet name reference there, too.

 

[Mechanic]

That did the trick!! Thank you very much, I appreciate your continued help.

Mechanic