Create Same Pivot but filter differnt value in each workbook

[Balajibenz]

Hi Team,

I have below code where I am creating new worksheet for each director in the list and named the worksheets to the directors name.

Sub test()
    Dim a As Variant
    Dim i As Long
    Dim ws As Worksheet
    a = Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1)
    For i = 1 To UBound(a)
        Set ws = ThisWorkbook.Sheets.Add(After:= _
                                         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = a(i, 1)
    Next
End Sub

I have two source data worksheets which are named as Source1 and Source2. what i am looking for is to create two pivot tables in each worksheets(that are created using above code) for the two sources as below.

VZ India Time data Anlaysis_6th June.xlsx
	A	B	C	D	E	F	G	H
1	Source1					Source2
2	Director	Apparao				Director	Apparao
3
4	Sum of YTD	Overall Cap				Sum of Net Billable Hours	Overall Cap/Exp
5	Level_6	Cap	Exp			AD	Cap	Exp
6	RAMADOSS, SENTHILNATHAN X (5688836822)	56.01%	43.99%			Ramadoss,Senthilnathan X	29.06%	70.94%
7	THIRUGNANAM, BALAJI (6577110287)	82.72%	17.28%			Reddy,Praveen A	10.94%	89.06%
8	THOTA, RAVI G (4815358132)	77.10%	22.90%			Thirugnanam,Balaji	53.93%	46.07%
9	REDDY, PRAVEEN A (2800398119)	30.02%	69.98%			Thota,Ravi G	73.28%	26.72%
10	KOVELA, RAM P (0372945772)	48.42%	51.58%			Grand Total	27.82%	72.18%
11	Grand Total	60.52%	39.48%
Apparao

fields and pivot tables will be same but only two filters needs to be changed.

1. Directors field needs to be filtered wrt the directors name(worksheet name)
2. any NA, null, blanks should be removed from Level_6
3. Cap and Exp fields valued should be in Sun and displayed as % of Row total. Thank you in advance.