Copy and rename

[adam]

Im trying to save to the sheet to a new workbook with a custom name and rename the sheet with the cell range date.

Im getting stuck in the line

Code: Select all

  wb2.Worksheets("Sheet1").Name = wb1.Sheets("re").Format(ws1.Range("E3"), "DD-MMM-YYYY") 'Set new name

Code: Select all

Sub R()
    
    Dim Path        As String
    Dim FName       As String
    Dim wb1         As Workbook
    Dim ws1         As Worksheet
    Dim wb2         As Workbook
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("re")
    Path = "G:\document\result "
    FName = Format(ws1.Range("E3"), "DD-MMM-YYYY") & ".xlsx"
    
    ws1.Range("A1:M100").Copy
    
    Set wb2 = Workbooks.Add
    
    With wb2.ActiveSheet.Range("A1:M100")
        
        Selection.PasteSpecial Paste:=xlPasteValues
        Selection.PasteSpecial Paste:=xlPasteFormats
        Selection.PasteSpecial Paste:=xlPasteColumnWidths
        wb2.Worksheets("Sheet1").Name = wb1.Sheets("re").Format(ws1.Range("E3"), "DD-MMM-YYYY") 'Set new name
    End With
    
    Application.DisplayAlerts = False
    
    wb2.SaveAs filename:=Path & FName
    
    Application.DisplayAlerts = True
    
    wb2.Close
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

any help would be appreciated.

[HansV]

I think you want

Code: Select all

          wb2.Worksheets("Sheet1").Name = Format(ws1.Range("E3").Value, "DD-MMM-YYYY") 'Set new name