Pivot automation

[narendramaruti]

Hi,

I am working on automation of an excel report, say the ReportExcel.xls. The data for this report is from a pivot that is present in another excel , say PivotExcel.xls. Right now we are manually copying data from PivotExcel to ReportExcel every month. When we are copying from PivotExcel to ReportExcel, we need create custom groupings, arrange the pivot items in a format to copy paste the values to ReportExcel.

To automate this step i did the following:
1) recorded a macro to group and arrange the pivotitems for the required format,copy Range(B16:C26) , say macro ArrangePivot()
2) From ReportExcel.xls i am opening the PivotExcel.xls file and then running ArrangePivot()
and pasting the selected Range(B16:C26) to ReportExcel.xls

But the problem in this procedure is , i am expecting the pivot should be in particular arrangement when each month you opened PivotExcel.xls or may fail when any of pivot items are re-arranged already by the time you opened.

Can any of you please advice how to automate this step in a more robust manner ?

Thanks,
Nmk