Wednesday, 29 October 2014

How to Save or Export Active Sheet Data into a New Workbook

Excel VBA Macro to Save or Export Active Sheet Data into a New Workbook

Method-I:

Sub Save_ActSht2New_WB()
    ActiveSheet.Copy

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs "C:\Users\Tamatam\Desktop\Temp\ActShtData.xlsx"
    ActiveWorkbook.Close True
    
    Application.DisplayAlerts = True
End Sub
----------------------------------------------------------------------------

Method-II:

Sub ExpActSht2New_WB()
'Step 1 Copy the Activesheet data
    ActiveSheet.Cells.Copy
    ActShtName = ActiveSheet.Name

'Step 2 Create a new workbook with Single sheet
    Workbooks.Add (xlWBATWorksheet)

'Step 3 Paste the data and change the sheet name to ActiveSheet Name
    ActiveSheet.Paste
    ActiveSheet.Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.Name = ActShtName

'Step 4 Turn off application alerts
    Application.DisplayAlerts = False
    
'Step 5 Save the newly created workbook
    ActiveWorkbook.SaveAs "C:\Users\Tamatam\Desktop\Temp\ActShtData.xlsx"
    ActiveWorkbook.Close True

'Step 6 Turn application alerts back on
    Application.DisplayAlerts = True

End Sub


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts