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

[Webbers]

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

Sub SaveAuditTab2()
    
    Dim FName           As String
    Dim FPath           As String
    
    Worksheets("Audits").Copy
    
    ' Name of File & file form
    ' File will be saved in same location as Original summary macro
    
    FPath = Sheets("Macros").Range("K9").Value
    FName = Sheets("Macros").Range("K14").Value & Format(Date, "MM-DD-YYYY").Text & ".xlsx"
    ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName
    
    ' Optional: close the new workbook
    ActiveWorkbook.Close
End Sub

[judgeh59]

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.

[judgeh59]

Also change this

FName = Sheets("Macros").Range("K14").Value & Format(Now(), "MM-DD-YYYY") & ".xlsx"

[Webbers]

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

[Webbers]

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.

Sub SaveAuditTab2()
    
    Dim FName           As String
    Dim FPath           As String
      
    ' Name of File & file form
    ' File will be saved in same location as Original summary macro
    
    FPath = Sheets("Macros").Range("K9").Value
    FName = Sheets("Macros").Range("K14").Value & Format(Now(), "MM-DD-YYYY") & ".xlsx"
    
    Worksheets("Audits").Copy
    ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName
        
    ' Optional: close the new workbook
    ActiveWorkbook.Close
End Sub

[judgeh59]

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]

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?

[Webbers]

judgeh59----

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

[Webbers]

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

[judgeh59]

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

[judgeh59]

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?

[Webbers]

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!

Sub SaveAuditTab()

Dim FileName As String
Dim Path As String

Application.DisplayAlerts = False

    'Directory is the same as where the workbook with the macro is stored
    FileName = ThisWorkbook.Path & "\" & Range("K13").Value & ".xlsx" 'Change extension here

    '  Change the format here which matches with the extention above.
    '  Choose from the following link http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
   
    '  Delete Specific Tab, Save & Close Workbook
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Macros").Delete
    ActiveWorkbook.Close SaveChanges:=True
    Application.DisplayAlerts = True
    
    ' Optional: close the new workbook
    ActiveWorkbook.Close

End Sub

[judgeh59]

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