Need Help Referencing Sheet Name in a Macro

[Curtis Campbell]

I have a workbook which contains a blank expense report document with a pivot table which summarizes the line item charges by accounting code. I created a macro which would copy the blank document, create a new worksheet and refresh the pivot table database (on the new worksheet) so that it looks to the appropriate columns on the new worksheet for its source data.

This seemed to work fine, but once I moved the document from my desktop to a production folder, the pivot table would not work. In looking at the code for the macro, I see what the problem is, but I’m not certain how to fix it. The highlighted section of code below references the ‘TEST NEW EXPENSE REPORT TEST.xlsm’ file as it existed on my desktop. Once you move the file to another folder or computer, it can’t work any longer. I’m sure there is some way to replace this pointer with an ‘ActiveWorkbook’ type pointer, but I don’t know how to implement this…can someone help??

Module1.png

[Richard Buttrey]

Hi and welcome to the forum.

Most of us prefer you to upload the actual workbook for things like this. Pictures are rarely much use (see guidance in the rules area). Upload the workbook and maybe someone will be able to assist.

[Curtis Campbell]

Thanks for the pointer! Here is the workbook in question.

[Curtis Campbell]

Bump - no response

[romperstomper]

To refer to the activeworkbook, you'd need:

Please Login or Register  to view this content.

[Curtis Campbell]

Thanks! That worked!