WorkAccountBro
New Member
Hello All,
I hope the title wasn't too confusing. Here's a little bit of background information. I work in mobile advertising and I receive an autogenerated report via email daily. This report has month-to-date information on "Impressions" and "Clicks" for several campaigns. With every day comes a new date added onto the new report, per campaign.
For example, yesterday I received a report broken out daily that showed impressions and clicks for every day in June for each campaign, with the final date being June 26th. Today, I received the same report, but with an additional row of information for June 27th for each campaign. Here's a screenshot with the dates circled; column A has the different campaigns FYI.
I then select the data and create a pivot table. I move the fields around and then select a campaign (a Report Filter),as seen in this screenshot. This other screenshot shows how I sort my fields in my pivot. I then copy the Grand Total and then paste it somewhere externally.
As you can imagine, it has been hard for a Macro newbie like me to figure out how to record a macro that can work on all subsequent reports because with every new day, each new report has rows added for each new day per campaign. So, while the data I need today ("Totals," as seen in the second screenshot) for campaign UNIVERSITY Oncology can be found in Row 30 of the pivot table, it will be in Row 31 tomorrow on the new spreadsheet.
Can anyone lend any guidance on how I can create a macro to deal with this?
I realize that you might think that selecting the "Date" field isn't necessary for the pivot table. While this is true since the totals are still the same whether or not my dates are shown, for my purposes I need to be able to see the specific Impressions and Clicks for the last few days for work-related reasons. So, in the second screenshot above, when I copy and paste the Grand Total, I am also making a mental note of what the impressions and clicks are for 6/26/2014 and even for 6/25/2014.
Perhaps I'll have to figure out a way for my macro to copy and paste the Grand Totals and the last two days as well...
Thank you in advance! File attached.
I hope the title wasn't too confusing. Here's a little bit of background information. I work in mobile advertising and I receive an autogenerated report via email daily. This report has month-to-date information on "Impressions" and "Clicks" for several campaigns. With every day comes a new date added onto the new report, per campaign.
For example, yesterday I received a report broken out daily that showed impressions and clicks for every day in June for each campaign, with the final date being June 26th. Today, I received the same report, but with an additional row of information for June 27th for each campaign. Here's a screenshot with the dates circled; column A has the different campaigns FYI.
I then select the data and create a pivot table. I move the fields around and then select a campaign (a Report Filter),as seen in this screenshot. This other screenshot shows how I sort my fields in my pivot. I then copy the Grand Total and then paste it somewhere externally.
As you can imagine, it has been hard for a Macro newbie like me to figure out how to record a macro that can work on all subsequent reports because with every new day, each new report has rows added for each new day per campaign. So, while the data I need today ("Totals," as seen in the second screenshot) for campaign UNIVERSITY Oncology can be found in Row 30 of the pivot table, it will be in Row 31 tomorrow on the new spreadsheet.
Can anyone lend any guidance on how I can create a macro to deal with this?
I realize that you might think that selecting the "Date" field isn't necessary for the pivot table. While this is true since the totals are still the same whether or not my dates are shown, for my purposes I need to be able to see the specific Impressions and Clicks for the last few days for work-related reasons. So, in the second screenshot above, when I copy and paste the Grand Total, I am also making a mental note of what the impressions and clicks are for 6/26/2014 and even for 6/25/2014.
Perhaps I'll have to figure out a way for my macro to copy and paste the Grand Totals and the last two days as well...
Thank you in advance! File attached.