Copy data from one sheet to a new workbook

Hello,

I have a data in excel and from which i made 4 pivot table on 4 sheets naming sheet1, sheet2, sheet3 & Sheet4.

now i have to copied these four sheets to a new workbook (Workbook name is equal to the value of cell B3 of sheet1 of workbook with data) having four sheets naming, Value, Unit, Product & Subproduct respectively.

I have predefined headers in these four sheets (in first 7 rows in all sheets). which are same in all sheets:

A2 = Name of workbook
A4 = MIS for the month
A6 = Prepared by : Rahul

I have to copy data of pivot table of sheet1 from cell A6 to end of pivot table and paste this to the new workbook's Sheet name Value in cell A8 to further.

same things is to do with other worksheets

Sheet2 to Unit.
Sheet3 to Product.
Sheet4 to Subproduct.

Please give me the macros to do this.

Thanks!

Rahul