Save a sheet instead of a complete workbook

Question

I am currently using following code to save an Excel workbook. Instead of saving complete work book, I just wish to save a sheet in this workbook named Reconciliation. All values in the sheet should be saved as values while keeping the formatting the same.

Sub Button3_Click()
    ' Yes
    ' Code to save consumer wise mirs on the desktop

    Dim Path As String
    Dim filename As String

    On Error GoTo Err_Clear

    Path = Environ("USERPROFILE") & "\Desktop\rohailnisar\"

    filename = Range("A1")
    ActiveWorkbook.SaveAs filename:=Path & filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Err_Clear:
        If Err <> 0 Then
            MkDir CreateObject("wscript.shell").specialfolders("desktop") & "\rohailnisar"

            Path = Environ("USERPROFILE") & "\Desktop\rohailnisar\"
            filename = Range("A1")
            ActiveWorkbook.SaveAs filename:=Path & filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

        End If
End Sub

user_9814069 · Answer

Export a Worksheet

This saves a copy of a worksheet as the only sheet in a new workbook in the same folder. Before saving, it converts formulas to values. It is saved in the .xlsx format 'removing' any code.
If the code is in the open (initial) workbook, then replace ActiveWorkbook with ThisWorkbook.

Option Explicit

Sub SaveWorksheet()
    On Error GoTo ClearError

    Dim swb As Workbook: Set swb = ActiveWorkbook
    Dim sws As Worksheet: Set sws = swb.Worksheets("Reconciliation")          

    Dim FolderPath As String: FolderPath = swb.Path & Application.PathSeparator
    Dim BaseName As String: BaseName = sws.Range("E1").Value
    
    Dim FilePath As String: FilePath = FolderPath & BaseName & ".xlsx"
    
    sws.Copy

    Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)
    dwb.Worksheets(1).UsedRange.Value = dwb.Worksheets(1).UsedRange.Value 
    Application.DisplayAlerts = False ' overwrite without confirmation
    dwb.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    'dwb.Close

ProcExit:
    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub

user_1140579 · Answer

Instead of saving complete work book, I just wish to save a sheet in this workbook named Reconciliation. All values in the sheet should be saved as values while keeping the formatting the same.

Code

Dim wbThis As Workbook
Dim wsThis As Worksheet

Dim wbThat As Workbook
Dim wsThat As Worksheet

'~~> Change this to the workbook which has the Reconciliation sheet
Set wbThis = ThisWorkbook
Set wsThis = wbThis.Sheets("Reconciliation")

'~~> This will create a new workbook with only Reconciliation
wsThis.Copy
    
'~~> Get that object. It will be last in the queue
Set wbThat = Workbooks(Workbooks.Count)
Set wsThat = wbThat.Sheets("Reconciliation")

'~~> Convert to values
wsThat.UsedRange.Value = wsThat.UsedRange.Value

'~~> Save that workbook
wbThat.SaveAs Filename:=Path & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Save a sheet instead of a complete workbook

2 Answers

Export a Worksheet

Code