Copy/Paste from Pivot Table with Different Years to Multiple Worksheets

[FinanceGeek94]

Hi everyone, I am new to this forum and am a summer student needing help at an internship. I'm dealing with sensitive information so unfortunately, I cannot post it here.

I am working with a Pivot Table with many fields which include different school names and years broken down into actual and budgeted revenues/expenses. Currently, I have an excel file created with a template for each school.

I am working with a Pivot Table with many fields which include schools, years, and their actual vs. budgeted revenues and expenses. I've been asked to create a variance analysis using that information. Currently, I have a basic template made so it will consist of one workbook per school and each workbook will have 10 sheets for 10 different years. I have been going in the Pivot Table and copying values from 2005-2006 and pasting it onto the relevant worksheet, but then I need to manually change the year to 2006-2007, and do the same thing. How can I automate this?

I've tried recording my filter but am not sure how to automate it to loop or go to the next year...Please help as I'm currently spending about 5 minutes on each worksheet just copying and pasting information in. Essentially, I am looking to have a code that will: - change the year to the next, copy and paste actual revenue, copy and paste budgeted revenue, then repeat but with a new year. After that, I'm looking for one to to be able to do the same but with expenses instead.

[CK76]

I'm dealing with sensitive information so unfortunately, I cannot post it here.

You can always make a small sample that represent your data set. Desensitizing data by replacing school names with innocuous names (Ex. SchoolA, SchoolB etc). And replacing financial value with fictitious numbers.

Also, clearly demonstrate how your end result should look like.

Without sample workbook, it is very difficult to give you solution that fits your need.

[FinanceGeek94]

Here you go as a visual, I crossed out the school names and city.
Pivot Table.jpgCapture.JPG

[FinanceGeek94]

Good idea! I just replied to this thread with screenshots. Essentially, I need to keep changing the year on the pivot table for the selected school and copy and paste into my excel file's worksheet for the same year. There are 4 tables total I need to copy and paste for each year: actual revenues, budgeted revenues, actual expenses, and budgeted expenses. I was thinking of creating two separate macros, one for revenues and one for expenses. Hope that makes sense! I just don't want to manually change the year on the filter each time and copy and paste by hand. Thank you for your help!

[CK76]

Please upload a sample workbook. Writing code based on image alone is difficult to say the least.