Answered

Error With Macro not running Properly

(0) Share

Report

Posted on by TerrySchmidt

Hi,

I'm learning to use power automate and I have an issue when trying to filter a pivot table with a Macro but when I call the Macro in power automate do not filter. When I run the Macro in excel VBA it works perfectly.
I Try renaming the macro, using parameters for the macro, killing all the Excel process.

Public Sub PivotsRefreshFilter()

 Sheets("Pivot").Select

 ActiveSheet.PivotTables("PivotTable1").ClearAllFilters
 
 ActiveSheet.PivotTables("PivotTable1").PivotFields("Filter").ClearAllFilters
 ActiveSheet.PivotTables("PivotTable1").PivotFields("Filter").CurrentPage = _
 "Last Closure"

End Sub

Run Macro.png

Thanks,

Terry S.

Categories:

Power Automate Desktop

All responses (7)

Answers (1)

Sort by

UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

Make sure in " Launch Excel " that the macro setting is enabled...
TerrySchmidt 19 on at

Like
a
(0)

Report
Copy link

Link copied!
Check is In place

Macro runs but do not perform the filter when running via power automate I add a check to write on a cell and that is done when running.

- Running manually it filters and write the that "It Works"
- Running in power automate does not filter but writes "It Works"

Public Sub PivotsRefreshFilter() Sheets("Pivot").Select ActiveSheet.PivotTables("PivotTable1").ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields("Filter").ClearAllFilters ActiveSheet.PivotTables("PivotTable1").PivotFields("Filter").CurrentPage = _ "Last Closure" Range("E1").Select ActiveCell.FormulaR1C1 = "It Runs" End Sub

Regards,
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

This might be the stupidest thing, but try removing the ( ) at the end of the Macro name in Run Excel Macro...they shouldn't be needed.
TerrySchmidt 19 on at

Like
a
(0)

Report
Copy link

Link copied!

Already try it, the weird part is that the last part of the macro that y put for control runs and write in the cell.

When running manually from excel all the tasks are perform.
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

That's very weird. The other option is to try running it through Send Keys:

*be sure to set the time between keystrokes to 100...Excel needs a slight delay to not run over itself. Notice the parenthesis are before the "w" because we don't want to hold alt down, just need to press alt and release before using the hotkeys to navigate the menu.

Send keys {Alt}()wmv
Wait (1)
Send keys PivotsRefreshFilter{Enter}

Good luck!
Verified answer

TerrySchmidt 19 on at

Like
a
(0)

Report
Copy link

Link copied!

It doesn't work, I delete and create all my file again from scratch and now it seems that is working. Maybe an issue with the original file that I create.
UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

Painful when you have to do it all again.
Ive had issues before when calling a macro from the current workbook or the personal workbook, the name must be correct, im sure you double checked but it does happen.