If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks.

When you create a macro, you are recording your mouse clicks and keystrokes.

Macro can be used via a button 

we can use macros in two ways either through absolute referencing or relative referencing  

Pivot Tables summarize and analyze small amounts of data into summary reports.

When you create a PivotTable, a copy of the data is stored in a pivot cache.   Any changes to the data won’t show up in the report until you refresh the cache.

you cannot create charts through pivot table, even if you are successful you may not be able to edit it.

Both macrod and pivot tables are parts of automation in excel

once you saved the file, you cannot edit pivot table  

The pivot table can be made either in new sheet or in the same sheet