Macro to copy 1 tab and save as xlsx based on multiple cell values

Okay, I have been fighting with this macro (and losing), I would appreciate any help that you can offer, and thanks in advance! Okay, I have this workbook, which is an xlsm file, that will be used by multiple users, all with their own copies of it, so I have tried to keep things as simple as possible. There are 2 tabs: "Macros" and "Audits". The Macros tab contains 3 macro buttons (one of which is for this one) and formulas. The second tab is Audits which contains the actual data, that is the tab I want to copy and save, as an xlsx file.

I want the file to be saved to the same directory where this workbook is (which happens to be in cell K9). The file name from cell K14 along with the date (which shows in Cell C13 as a Concentration formula) to let the user know what the final file will be named. Now when I run the code shown below, I get a Run-time error '9' Subscript out of range.

Cell Reference	Label	Data
Cell K9	File Path	C:\Special Projects\Daily reports
Cell K10	Today's Date	6/19/2015
Cell K12	Current File Name	Audit Summary
Cell K13	New File Name	AM Audit Summary - Fox - 06-19-2015
Cell K14	Requested File name	AM Audit Summary - Team C

Please Login or Register  to view this content.

move the Worksheets("Audits").Copy after the FName line and before the SaveCopyAs line

you are setting the variables after you copy and the that worksheet isn't there in that instance.

Also change this

Please Login or Register  to view this content.

judgeh59----

Thanks for responding. I made the modifications you requested, however once I run the macro, it copies the tab and then I am prompted with "Do you want to save the changes you made to 'Book 14'?". If I select Save it did open the prompt in the directory I wanted, but it is not naming and saving the file on it's own. Am I missing something? Did I modify the code correctly? below is my revised code... just in case.

Please Login or Register  to view this content.

when I plug your code in to my XL it works fine....

so a few thing may be happening...

are the path structures correct in Cell K9, because if it isn't then it will open a Save As dialog box...

also, have you tried stepping through the code to make sure all the variables are being assigned the way you think they should be?

let me know if you have never "stepped" through the code....

also, using the local windows will allow you to look at the variables while stepping through the code...

If you already know how to do these thing please forgive me for being too verbose....

judgeh59-----

First of all, cell K9, the file path is a formula. However I verified the path, and copied it into cell K8, then changed the cell reference in the macro to see if anything different would happen... same thing happened.

No, I am not familiar with the "stepping through the code" process, and I greatly appreciate any guidance you can offer. If I did this set process correctly... the variables showed """ for both FPath and FName, however when I got to the SaveAs portion and moused over.... the path was listed (for FPath), and then when I moused over FName, it shows FName = "AM Audit Summary - Fox06-1-9-2015.xlsx". Now I can see those are both listing actual values, and aside from the fact that the file name needs to include a space, or actually " - " between the file name and where the date is added, it looks correct to me.

6-19-2015 12-39-46 PM.png

sounds like you might be doing it anyway, but basically it's pressed F8. By doing this it executes the highlighted line. While in the VB editor click in in View in the ribbon and click on Locals Window. It does the same as mouseover but you can examine without actually doing to the mouse over....

Is it possible to upload a file with any company confidential stuff removed?

judgeh59----

okay, I did like you said, and the path shows correct... and aside from that lack of needed " - " before the date, the file name is correct, however this same issue continues to happen.

6-19-2015 1-07-28 PM.png

and the path is exactly like that....case sensitive?

judgeh59----

yes, exactly... I douible checked and triple checked.

I'm back to 2 things....upload a file so I can test it....

and the other is, do you have write permissions on that file structure?

judgeh59----

Wanna say thanks for all your help on this. Over the weekend I was thinking about this, and I decided to approach my issue from a different direct... and OMG... it worked! I decided that since there are only 2 tabs, instead of trying to copy the one I need, since I require a filename from cell references, that I would simply save a copy of the whole workbook, then delete the tab I did not need... and it is actually easier! Not to mention I was able to resolve it all on my own. This is what I use, in case it can help anyone else... and thanks again!

Please Login or Register  to view this content.

always fun to fix your own problem....well thanks for the rep point and have a great day....