Sheet Name Change - Macro Doesn't Work

[GregStewartPTC]

I'm running into an error with a macro that is used to create a pivot table for some work reports. The problem I'm running into is when the report is exported, the sheet has a slightly varied name depending on which department it is for. I found a simple workaround by editing the sheet name to a short acronym before running the macro, and it works great. However, I would like to completely automate the process if possible. I know that you can use the sheet code name in instances like this, but no matter how I apply it to the code I receive the same error - Run-time error 5: Invalid procedure call or argument. Here is the code that shows in the debug:

Please Login or Register  to view this content.

Where NR = the sheet name I'm referring to for the pivot table. Any and all help is appreciated. Thank you in advance.

[Tinbendr]

Is the NR sheet active when this runs? If so, then maybe:

Please Login or Register  to view this content.