VBA Combining Data and Create Pivot

[unknownymous]

Hello Gurus,

Hope you are doing well despite the pandemic. I was wondering if you have any idea on how to code this:

1. I have a worksheet with 5 tabs or more (may vary). The headers are same (see below):

Number	Name	Sales Div	Data	Note
1	Sam	SA	100
2	Amy	FA	90

2. I want to create a tab named as "Consolidated" by combining the data of all tab available on the same worksheet.
3. In the Consolidated tab, there will be additional column whereas the tab name is indicated for reference.

Example:

Number	Name	Sales Div	Data	Note	Tab Name
1	Sam	SA	100		Sheet 1
2	Amy	FA	90		File 101
3	Ron	TP	100		Subject 01

4. Lastly, a Pivot will be created by getting the Tab Name and Sum of Data from the Data column

Tab Name (Row Name)	Sum of Data
Subject 101	100
Sheet 1	100
File 101	90
Grand Total	290

Any thoughts will be much appreciated.