Run code from another workbook

[L. Howard]

I am trying to do as the Subject says.

I found a site that has info on that.

<*******

Sub CallCodeFromAnotherWorkbook()
Application.Run "AnotherWorkbook.xls"!OtherMacro
End Sub

To replicate:
1.Open both the example workbooks
2.Press the button.
3.You will then see a message box saying that the other code is being run.
4.The Syntax for Application.Run is: "WorkbookName.xls!MacroName"
5.Using this tip, you can run all the macros from one workbook using another workbook.

<*******


I down loaded their two books and after correcting what I guess was a typo, it did work.

Everything I've tried with my Books fails.

I have three Books and I am try to call two macros from other books from the book long. The books are:

long
Book_A
Book_AA

Macro in the book long is:

Sub BooK_A_Book_AA_Macro_Call()
Application.Run "Book_A.xls!AbookToLong"
Application.Run "Book_AA.xls!AAbookToLong"
End Sub

The Macro in Book_A is:

Sub AbookToLong()

The Macro in Book_AA is:

Sub AAbookToLong()


Notice the extensions are .xls (saved as 97-2003). The download example came as .xls.

I have tried saving everything as Excel Macro-Enabled and have the .xlsm extension but nothing works.

No error number, just yellows out the first line of Application.Run...etc.

I have tried calling only one macro and that does not work either.

Seems to like it should work with Excel 2010.

Do you see anything I'm doing wrong?

Thanks.
Howard

 

[L. Howard]

I was unable to copy the macro from the site so I typed it in here and I made a typo.

This is NOT the typo I mentioned in previous post.


Sub CallCodeFromAnotherWorkbook()
Application.Run "AnotherWorkbook.xls"!OtherMacro
End Sub

Miss placed the second "

Sub CallCodeFromAnotherWorkbook()
Application.Run "AnotherWorkbook.xls!OtherMacro"
End Sub

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 17 Feb 2014 03:24:35 -0800 (PST) schrieb L. Howard:

I have tried saving everything as Excel Macro-Enabled and have the .xlsm extension but nothing works.

No error number, just yellows out the first line of Application.Run...etc.

for me it works fine.
Could there be trailing spaces into the workbook name?
Try:
Application.Run "'Book_A.xls'!AbookToLong"
Application.Run "'Book_AA.xls'!AAbookToLong"
with single quotes around the name


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Mon, 17 Feb 2014 03:24:35 -0800 (PST) schrieb L. Howard:






for me it works fine.

Could there be trailing spaces into the workbook name?

Try:

Application.Run "'Book_A.xls'!AbookToLong"

Application.Run "'Book_AA.xls'!AAbookToLong"

with single quotes around the name





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

That is encouraging.

Could you put your example on the Skye thing, time permitting.

May help me trouble shoot my problem.

Meanwhile I will give things another doing over.

Thanks.

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 17 Feb 2014 04:30:22 -0800 (PST) schrieb L. Howard:

Could you put your example on the Skye thing, time permitting.

have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
All three books are there


Regards
Claus B.

 

[L. Howard]

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

All three books are there





Regards

Claus B.

--

Yes, that helped and that now works.

I moved everything into standard modules, where it probably should have been to start with.

Then myRng stopped copying to the sheet Long, no error, just no data transfer, except the two columns near the bottom of the code. So, since those two columns are still copying to Long I figured I needed to add wksSource to the Set myRng line. But that line now errors out with an object variable not set. It works with the columns down below, is it different with an array?

Howard



Sub AbookToLong()
Dim myRng As Range, MyRng1 As Range
Dim rngC As Range
Dim i As Long
Dim myArr() As Variant

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim rngSource As Range, rngTarget As Range

'/***
Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")
'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")
'/***

Set wkbSource = Workbooks("Book_A.xlsm")
Set wkbTarget = Workbooks("long.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

Application.ScreenUpdating = False

For Each rngC In myRng
ReDim Preserve myArr(myRng.Cells.Count - 1)
myArr(i) = rngC
i = i + 1
Next

With wksSource
wksTarget.Range("M2").Resize(columnsize:=myRng.Cells.Count) = myArr
End With

wksSource.Range("C7:C18").Copy
wksTarget.Range("X2").PasteSpecial Transpose:=True
wksSource.Range("C33:C50").Copy
wksTarget.Range("AJ2").PasteSpecial Transpose:=True

Application.ScreenUpdating = False
End Sub

 

[Claus Busch]

Hi Howard,

Am Mon, 17 Feb 2014 05:55:19 -0800 (PST) schrieb L. Howard:

Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")
'Set myRng = Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")
'/***

Set wkbSource = Workbooks("Book_A.xlsm")
Set wkbTarget = Workbooks("long.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

you have to set the workbooks and the sheets before setting the range:

Set wkbSource = Workbooks("Book_A.xlsm")
Set wkbTarget = Workbooks("long.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("Sheet1")

Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Mon, 17 Feb 2014 05:55:19 -0800 (PST) schrieb L. Howard:










you have to set the workbooks and the sheets before setting the range:



Set wkbSource = Workbooks("Book_A.xlsm")

Set wkbTarget = Workbooks("long.xlsm")

Set wksSource = wkbSource.Sheets("Sheet1")

Set wksTarget = wkbTarget.Sheets("Sheet1")



Set myRng = wksSource.Range("AV2,AW4,X20,V20,AN2,AO4,X8,AF2,AG4")





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Aaaaarrgh!! You know, I knew that! Dang, too absorbed with the other pesky task .

Thanks.

Howard