ThisWorkbook Help

[OcMac]

Hello all,

I have a workbook that i have been adding code to that works now. I will not give that workbook out to others on the team to use for their own. The problem is that I have all the paths pointing to my location and named MattsCalendar2013.xls. The other members will be renaming it to their own names so I don't want to go into he code and replace the path with each of them that would take to long.

I was hoping to use ThisWorkbook to set the path, but not sure how to do it. The code in the workbook is activated by clicking a control button. So I was hoping that once the button gets clicked, a generic name like CurrentWorkbook would store the path and name of the open workbook so it could be used in the code later.

I will use it to activiate the workbook later in the code right now it says

#Workbooks("MattsCalendar2013VBACode.xlsm).Worksheets("Matt's Vacations").Activate#

I know I can get around the worksheets by referring to the worksheet number instead of name, so Worksheet("sheet1"), but not sure about the workbook.

Thank you for your help,

Matt

[mmogharreban]

Hi Matt,

Have you considered dictating where the file is saved as opposed to copying where excel chooses to save it?

Something like the below would allow you to create a new folder at the path of your choosing based on cell values.

Please Login or Register  to view this content.

Just a thought

[OcMac]

Thank you very much for the thought.

That might work for others but I don't see it working here. Our people copy the "master file" and then change it for each of their regions with different information and save it to their desktop or their network drive.