How to create/record macro to save the sheet in a separate excel & perform some action on the sheet

In the last article, we learnt what macro is and how to create it for performing repetitive tasks on the same work book, just on a single click of button.

In this article, we will be learning about how to create macro to take data from current workbook sheet, perform manipulation in new book and save it at desired location just on the click of keyboard short cut. During the manipulation we will create a pivot table.

This is the basic example with limitation that once macro is run. You would need to close and reopen macro enable excel before running it again. In upcoming articles, we will talk about removing this limitation.

The sample macro enabled excel can be accessed from here. On running the macro on the click of 'Ctrl + y ', It generates the excel at 'C:\temp\excel\NewWorkbook' location. Please ensure that this location exists in your computer before running the macro.

If you wish to create the macro using coding rather than recording. Sample code can be accessed from here.

Sub generateSummary()
'
' generateSummary Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy
    Windows("NewWorkBookCreationMacro.xlsm").Activate
    Windows("Book1").Activate
    Range("A1:G44").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R44C7", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet1!R2C9", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet1").Select
    Cells(2, 9).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Units"), "Sum of Units", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Total"), "Sum of Total", xlSum
    ActiveWorkbook.SaveAs Filename:="C:\temp\excel\NewWorkbook\NewExcel.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

The step by step method of recording & executing the macro is as following -

Step#1 - Save the initial file as macro enabled excel workbook.

Step#2 - Start recording the macro and enable it to run via any key combination like Ctrl+y

Step#3 -Click on option to create new workbook and copy the sheet in the newly created workbook.

Step#4 - In the new workbook perform required changes

Step#5 - Save the new workbook to the desired location and close.

Step#6- Stop the macro recording ,save and close

Step#7 - Delete previously generated NewExcel.xlsx. It will get generated again by running the macro.
Open the macro enabled excel file again. Run macro using Ctrl+y key combination