Macro to update Aging Report

Hello,

I need your help in creating a Macro for preparing a Aging Report.

In the sample sheet I have Raw Data sheet and in that it has to look for Close time column and if it is empty it need to copy the data in another sheet named Aging Data (As in sample sheet).

I need the Pivot table as shown in the sample data and in that I have added Column H & I using today () function & below code manually. Please help me to automate it.

Please Login or Register  to view this content.

Note: The data is not constant and vary.

Thanks in advance for your help!!

Regards,
Humac

Do you need the 2 calculations to be done in the pivot sheet itself? Or how about doing it in the Aging data tab and then pulling it over to the pivot? Or we could use a calculated field for the pivot (which is going to be a lil complicated).

Will look into it today.

I have modified your copy code as well. This is an end-to-end code which involves - filtering and copying data, creating the pivot, adding the 2 calculation fields. On 2nd thoughts, i retained the calculations in the pivot tab itself as it was not giving proper values if included in the Aging Data tab.

Please Login or Register  to view this content.

I can change it no doubt. However, as per your instructions, i had done the pivot on the Aging Data as the pivot reflects how many days the lines are aged right?

What do you mean by -

I hope it is preparing the pivot table using both Aging Data & as well as Pivot Data.

Actually the pivot tables sheet only contains the pivot. There is no data in there. So the pivot is taking the data from the aging data tab. The reason you are seeing 2 in the pivot and not 1 is because there would have been data existing in the Aging data tab before you ran the macro. Once you ran the macro, the data got added at the end. If you want, i can have the Aging data cleared before the fresh data is inputted from the Raw data tab. Would this work?

You actually dont need to prepare the data in the Pivot Tables sheet. It will automatically take the data from the sheet you want. So is the following fine ? -
- Do not clear the aging data before putting in fresh data.
- Point the pivot table to take the data from the Raw data tab.

May i ask the purpose of populating the Aging data tab if you are not using it for the pivot?

Use this updated code -

Please Login or Register  to view this content.

Originally Posted by humacdeep

1. Can the Macro create the Aging Data & Pivot_Tables when I run the Macro? (Current code is throwing error if Aging Report is is not there, it is creating Pivot_Tables Sheet) or

The macro considers that the "Aging Data" tab is already present with headers. If required, this can be created when the macro is running.

2. Can it update the same Aging Data & Pivot_Table Sheets when ever I run the Macro? (Current code is throwing 1004 error when I run the code for the second time)

Please Login or Register  to view this content.

Are you running the code with the "Pivot_Tables" sheet already present? The code will also create a separate "Pivot_Tables" sheet each time the code runs.

3. I want same dates to be Grouped in Pivot Table, will it be possible?

What kind of grouping, maybe you can explain some more.

Please change the code to create the Aging Data Sheet with Headers.

What kind of grouping, maybe you can explain some more.

If there are 5 rows with same dates, I want to Group all of them in to single row in Pivot Table using group option (as in sample data)

Regards,
Humac

Use this revised code. It will check if the Aging Data tab is already present. If it is, it will clear it up of all data. If not, it will insert the sheet and the header.

Please Login or Register  to view this content.

I didnt find any sample for the grouping. If you can attach a file, i will be able to work on it for you.

Ok..i understood the problem. What have you done in the Aging Sample to avoid the "time" from appearing in the Request time field? If i remove the time, then i will be able to group it. Actually the pivot itself will group it.

Find the updated code here:

Please Login or Register  to view this content.

Add this code above the last "End With" in your code -

Please Login or Register  to view this content.

You are welcome

Arlette,

I have run this in my orginal Data and I am getting Correct Aging Data but the Data in the Pivot_Tables is not correct, I hope it is pulling data from RawData instead of Aging Data to get the Pivot Table

I have attached the report. Please have a check.

Regards,
Humac

Yes. The reason for the discrepancy is that the "Raw Data" tab also includes those where the Close Time is not empty. I noticed that your pivot is referencing the "Aging Data" tab, but you asked me to reference the pivot to the "Raw Data" tab. The raw data contains all the data and will not give you a true picture of the aging items.

I have changed the code. Find updated code here :

Please Login or Register  to view this content.