Using macro to remove unwanted fields and then create Pivot Table in Excel 2010
Posted on 2014-09-25
Macro should do the following in sequence:
1. Remove all X the prefix in cells at column H, for example if H5 contains "XYAB" then X should be removed and the result is "YAB"
2. Create a pivot table using data at the highlighted area.
for example if F46:H82 is highlighted, a pivot table will automatically created where the following headers are created for the pivot table:
F46 = No
G46 = Wt
H46 = Cty
and the pivot table list:
Row Labels = Cty
Sum Values = Sum of No
Location of the pivot table will be in a new sheet named "Pivot [Previous Mth-YY]", ie if i were to create this pivot in the month of Aug 2014, the the sheet name will be "Pivot Aug14".