Calling macro in and xlsm workbook not working

vbanovice123

Board Regular
Joined
Apr 15, 2011
Messages
91
Hi,

When I try to invoke a macro while being in the active macro enabled (xlsm) workbook, I get an error.

The workbook in which the called macro exists is also an xlsm workbook.

Can we not call or invoke a macro in an xlsm workbook?


Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="C:\Users\All\Downloads\OriginationsMonitor.pptm"

slidenum = 7
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Vol", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 8
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Refi", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 10
'HFI
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "HFI", "HFI13M", slidenum, arheight, arwidth, artop, arleft, 1


Application.Run "C:\Users\All\Downloads\'OnePagersYearQtr_Template.xlsm'!Copy2PowerPoint2"

Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
Call Copy2PowerPoint2
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I tried the below in the workbook that contains the main macro and calls the other macro as below:

No error but nothing happened



Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="C:\Users\Pradeep Family\Downloads\OriginationsMonitor.pptm"

slidenum = 7
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Vol", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 8
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Refi", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 10
'HFI
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "HFI", "HFI13M", slidenum, arheight, arwidth, artop, arleft, 1


'PPTM.Presentations.CheckOut (OriginationsMonitorPackage)
'Application.Run "C:\Users\Pradeep Family\Downloads\'OnePagersYearQtr_Template'!Copy2PowerPoint2"

Application.ScreenUpdating = True

End Sub

Sub Test()

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\Myfolder\Downloads\'OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPoint2

End Sub
Private Sub CommandButton1_Click()
Call Copy2PowerPoint2

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\MyFolder\Downloads\'OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPoint2
End Sub


Not sure why the macro within the workbook being called is not executing?
 
Upvote 0
You've got an apostrophe in from of OnePagersYearQtr_Template.xlsm in the last two instances, could it just be a syntax issue?
 
Last edited:
Upvote 0
I got rid of the apostrophe . Still the call to the second macro within the active workbook that contains the first macro does not work. I want to be able to call multiple macros that exist in multiple workbooks plus run the macro in the active workbook.

Please help . I cannot figure out what I am doing wrong.



Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="C:\Users\Myfolder\Downloads\OriginationsMonitor.pptm"

slidenum = 7
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Vol", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 8
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Refi", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 10
'HFI
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "HFI", "HFI13M", slidenum, arheight, arwidth, artop, arleft, 1

Application.ScreenUpdating = True

End Sub

Sub Test()

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\Myfolder\Downloads\OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPointQtr

End Sub
Private Sub CommandButton1_Click()

Call Copy2PowerPoint2
'Call oWb.Copy2PowerPointQtr

End Sub



Sub CopyTo13MonthHardCopy()

'
' CopyHardCopy Macro
'

'
'HFI
'copy_range_sheet(sheet, rngname)
copy_range_sheet "HFI", "HFI13M"

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()

Call CopyTo13MonthHardCopy

End Sub
 
Upvote 0
I also tried changing the name of the macro that was called. Originally, the macro in the active workbookand the macro in the called workbook had the same name and I thought this could be an issue. So I renamed the macro in the called workbook. But that did not help.
 
Upvote 0
I just looked around and maybe something as simple as

Application.Run "Workbook1!MyMacro"

would work. I tried it on some sheets I have and it works as long as the workbook you are calling has no spaces in the name and both files are saved in the same directory.
 
Upvote 0
What type are the workbooks. I have the .xlsm workbooks. I tried the below and still no luck.

Sub cals()
'Run a macro in another workbook
Application.Run "'OnePagersYearQtr_Template.xlsm'!Sheet22.Copy2PowerPointQtr"
End Sub
 
Upvote 0
Mine are both .xlsm...Try without the apostrophe's, I think if you don't have an actual space, but rather an underscore, you shouldn't need anything more than quotes on the outsides. I have both sheets open when running it, so extra code to open the other sheet may be required, but here is my test that works for me.

Code:
Sub Macro1()

Application.Run "GemSheet.xlsm!FilterGems"

End Sub
 
Upvote 0
I tried keeping both templates(.xlsm) open. It still did not work.

Question: Where should I place the subroutine that calls the macro in the second workbook? I might be placing incorrectly.

I place it separately and then I have the command button that calls the macro within the active(master) workbook.

Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="C:\Users\Pradeep Family\Downloads\OriginationsMonitor.pptm"

slidenum = 7
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Vol", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 8
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Refi", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 10
'HFI
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "HFI", "HFI13M", slidenum, arheight, arwidth, artop, arleft, 1

Application.ScreenUpdating = True

End Sub

'Sub Copy_YearQtr()

'Dim oWb As Workbook

'Set oWb = GetObject _
'("C:\Users\Pradeep Family\Downloads\OnePagersYearQtr_Template.xlsm")

'Call oWb.Copy2PowerPoint2

'End Sub

'Sub cals()
'Run a macro in another workbook
'Application.Run "'OnePagersYearQtr_Template.xlsm'!Sheet22.Copy2PowerPointQtr"
'End Sub

Private Sub CommandButton1_Click()

Call Copy2PowerPoint2

'Application.Run "OnePagersYearQtr_Template.xlsm!Sheet22.Copy2PowerPointQtr"

'Call Copy_YearQtr

'Call oWb.Copy2PowerPointQtr

End Sub


Sub Copy_YearQtr()

Application.Run "OnePagersYearQtr_Template.xlsm!Sheet22.Copy2PowerPoint2"


End Sub


Private Sub CommandButton1_Click()

Call Copy2PowerPoint2


End Sub
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,459
Members
452,915
Latest member
hannnahheileen

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