Excel Macro throwing Runtime error 5 when file is shared!

I've googled all day and need help fixing this issue.

I've recorded a macro for a data to create a pivot table and filter data, both copied to different sheets. When I run the macro, it works fine. I have emailed the workbook to my colleague and when he tries to run the macro, it throws an error "Run-time error '5': Invalid procedure call or argument". I have tried all possible steps to fix this but nothing worked. Below are few steps I've tried and checked:

1. Tried saving the excel file as .xlsx and macro module as .bas separately and sharing the files. Then importing the macro module and saving the workbook as .xlsm
2. Tried enabling the Macro security feature to run all macros on my colleague's system.
3. Sharing the file through a completely different medium.
4. Enabled Developer Tab on my colleague's system.
5. Ensured there's no space between the words in sheet tab name.

One thing I observed is, I set a color format to certain cells in pivot table which the macro creates automatically when run. And when I delete the data to run the macro again, the color formatting is gone, on my system. When I try the same on my colleague's pc, data is deleted but color formatting is still there indicating there's some issue. I have no idea what it is.

---

Please Login or Register  to view this content.

When I run the macro on my colleague's system, click on Debug on the error pop-up, this is what I see.

Macro error.PNG
Macro error_2.PNG

Any help is much appreciated! Thanks.

Whether masterdata worksheet exists in that workbook?

Please post the screen shot of the error too.

Does he have Office 2016? If not, you can't use 6 as the version number.

You can leave it out, as it's an optional argument.