Recording Macro to create a Pivot Table in Excel 2013: run time error 5 invalid procedure

Hello All,

Upfront, I don't know much about VBA programming and am still learning a lot in Excel so please be patient with me. Here is my issue, I was asked to work on a small project at work, the dept needs to export data from Access into an Excel worksheet and count the number of students in each grade per school.

I figured the simplest thing to do would be to create a pivot table. The dept will be doing these counts pretty frequently as they add students to list in Access, I figured recording a macro would simplify things as they can run the macro and it will sort, create the PT and provide a last run date.

When I run the macro I get an error: "Run time error 5 invalid procedure call" code provided below.
Can someone please help me with this problem? What am I doing wrong?

Thank You all in advance.

Please Login or Register  to view this content.

Full Code for entire Macro:

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added them for you this time. Please read the rules and follow them in the future)

I reckon the simplest thing would be to link the pivot table to an Access query and then just refresh it when necessary. no code required :-)

Another alternative is to run an Aggregate query in Access and export the results directly to Excel. No need to run a PT. Assuming you have a field for the Grade and the Student Name

Please Login or Register  to view this content.