I am currently using following code to save excel workbook, what I need is that instead of saving complete work book, I just wish to save a sheet in this workbook named Reconciliation. All values in sheet should be saved as values while keeping the formatting same.

Sub Button3_Click()
    'yes
    'code to save consumer wise mirs on 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

CodePudding user response：

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 the first occurrence of ActiveWorkbook with ThisWorkbook.

Option Explicit

Sub SaveWorksheet()
    On Error GoTo ClearError

    Dim swb As Workbook: Set swb = ActiveWorkbook
    
    Dim FolderPath As String: FolderPath = swb.Path & Application.PathSeparator
    Dim BaseName As String: BaseName = swb.Range("E1").Value
    
    Dim FilePath As String: FilePath = FolderPath & BaseName & ".xlsx"
    
    swb.Worksheets("Reconciliation").Copy

    Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)
    dwb.Worksheets(1).UsedRange.Value = dwb.Worksheets(1).UsedRange.Value 
    dwb.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook
    'dwb.Close

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

CodePudding user response：

what I need is that instead of saving complete work book, I just wish to save a sheet in this workbook named " Reconciliation". all values in sheet should be saved as values while keeping the formatting same.

Is this what you are trying (UNTESTED)?

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