Using date as prefix for file name and saving it to local drive

Hi all,

Thanks for looking into this.

Range("C4") = 03/31/2015

Try to save the workbook as "2015/03/31 ABC" as file name


This part of the code in red errors out:


With ActiveWorkbook
.Sheets(1).Cells(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
.SaveAs "C:\TEST\" & sDate & sItem & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
.Close
End With

Sub PivotStockItems()

    Dim i As Integer
    Dim sItem As String
    Dim sDate As String
    
    sDate = Format(Worksheets("pt_AR_AGING_USD").Range("C4"), "yyyy/mm/dd")
    
    Application.ScreenUpdating = False
    Worksheets("pt_AR_AGING_USD").Select
    With ActiveSheet.PivotTables("PivotTable2")
        .PivotCache.MissingItemsLimit = xlMissingItemsNone
        .PivotCache.Refresh
        With .PivotFields("MBU")
            '---hide all items except item 1
            .PivotItems(1).Visible = True
            For i = 2 To .PivotItems.Count
                .PivotItems(i).Visible = False
            Next
            For i = 1 To .PivotItems.Count
                .PivotItems(i).Visible = True
                If i <> 1 Then .PivotItems(i - 1).Visible = False
                sItem = .PivotItems(i)
                Cells.Copy
                Workbooks.Add
                With ActiveWorkbook
                    .Sheets(1).Cells(1).PasteSpecial _
                        Paste:=xlPasteValuesAndNumberFormats
                    .SaveAs "C:\TEST\" & sDate & sItem & ".xlsx", _
                        FileFormat:=xlOpenXMLWorkbook
                    .Close
                End With
            Next i
        End With
    End With
End Sub