Copy 4 sheets from template and dynamically name, Save template and leave workin file open

I have a code that currently works, however it does not complete my task. Okay I have variables on Sheet5 "codes" (I prefer the code names to the sheet names... just in case someone renames them).

sheet5 variables:
C15 - Concatenation of new filename desired (c22 & I18)
C22 - "base" file name
I18 - date added to filename (added in format of mm.dd.yy)

There are a total of 6 sheets in my template (where the macros are). The ones being copied are Sheet1, Sheet3, Sheet4, Sheet5. The desire is to copy those 4 sheets to a new workbook, name the file using the base name and the date, save as an xlsx (no macros) to the same directory that the template is located. I also want to have sheet1 and sheet5 hidden. Leave the NEW file open, save and close the template.

Currently, the macro copies those 4 sheets to a new workbook, however I cannot get the template files to save and close, or the new file to hide those 2 sheets. I am close.

Please Login or Register  to view this content.

Greg M.

Thanks for the response. It produced an error. Attached are screenshots. sheet1 & sheet5 are hidden, and sheet3 & sheet4 are displayed for the user. I am not sure what happened. Again, we get to the saving the file part when we have the errors.

Greg M.

I decided to try something, I moved my template from a development folder (for today) to my download folder... shorter path. I used your last code, and ran it a few times, most of the macro worked just fine. The macro:
1) copied the 4 sheets
2) hid the 2 sheets I wanted it to
3) Renamed the new file based on the dynamic variables to the same path as the template
4) The new file was left open (for the user to work)

The only thing that did not happen is the original file (the template) needs to save and close after everything is done. Can ya help?