Macro to Locate and Delete File saved from cell variable

Long Story Short - or atleast as best I can !

A macro creates a Workbook and saves it as WORK ORDER X, PART X, PRODUCTION.xlsm on a Shared Drive. The Work Order and Part# will always be variables as the same Master Workbook is used for several different models.

Example: In the Summary Folder, I may have the following files:
WORK ORDER 12345, PART MM176, PRODUCTION.xlsm
WORK ORDER 45678, PART MM123, PRODUCTION.xlsm

At the end of the Work Order, the users presses a button to resave file as WORK ORDER X, PART X, REWORK.xlsm to alert the shift leader that production is complete and reworks can be started.

The only problem is this leaves the original PRODUCTION.xlsm file in the folder still and creates confusion. I need the PRODUCTION.xlsm to be deleted when the .REWORK.xlsm is created and I end up with:
WORK ORDER 12345, PART MM176, PRODUCTION.xlsm
WORK ORDER 45678, PART MM123, PRODUCTION.xlsm
WORK ORDER 45678, PART MM123, REWORK.xlsm

I want to delete the old PRODUCTION.xlsm through a macro which can be stored in the PRODUCTION Workbook so the Shift Leader does not have to go in and remove the old .PRODUCTION.xlsm file. I do not want to move it to a different folder for 2 reasons - 1) I want the Shift Leader to be able to go in to one folder and find everything he needs - Both the .PRODUCTION and REWORK files (computer illeterate but getting better), 2) I do not want duplicate files created.

Maybe I am looking at this from the wrong angle, it should be simple but.....the fact that it saves based on a variable has me spun! Have attached code below. Please tell me this is easier than I making it out to be!

HTML Code: 

You will have to work with your existing code structure to make this work (set the variables, test if the file exists, error check, etc), but a quick example is below. This code posted deletes a file named TestFileAddIns.xlsx which is saved on my Desktop.

Please Login or Register  to view this content.

The issue is created becuase I am using a variable - it will never have the same name. The original form is a template that can not be over writtien. The same template will produce different files, it will not always save as "TestFileAddins.xlsx". Using your example above, it would be named as TestFileAddins PARTXXXX.xlsx - The PARTXXX will always be a variable.

I understand that, which is why I suggested that you adapt the code to meet your specific requirements. For example, instead of hard coding fName, you can create a string to reference the specific file based on your variables. How would the file programmatically know which file to delete? As an example, let's say that the part name is stored in cell A1. The code would look more like:

Please Login or Register  to view this content.

Does this help?

going to give it a try tonight and let you know. I think the part you just added was what I couldn't wrap my head around. I tried it a few different ways but still couldn't get it! Thanks.

Works like a charm! Brilliant!

Glad we got it to work.