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
Bookmarks