Autopopulating data to multiple tabs from master tab

New Contributor


I have a master worksheet - it's a month view calendar. 

I have to input data into this calendar for 5 separate business units. 
I then have to extract that information into separate tabs for each of those business units - but only showing the information for their own business unit. 

It will change monthly. And obviously the calendar itself will also change monthly. 
Is there a way to 

1. Update the calendar on Master, and have that update throughout the rest of the tabs

2. Extract information for each business unit and have that populate onto their own tab

Any information would be appreciated! thank you 

3 Replies

@BCAdmin2021 I guess this could be done with (power) pivot tables and/or Power Query. How exactly depends on the structure of the data in your master sheet and on how the output should look like. 


So, if you could upload a workbook containing some (fake) master data and the desired output by business unit, that would be helpful.



Thank you for your response


Master need to show all information 

This information then needs to be extracted onto each colour coded tab

@BCAdmin2021 My goodness! This isn't very structured. I had hoped for a list of tasks in a tabular format like Date, Task, Unit from which you could create summaries per Unit, but not necessarily looking like a traditional calendar. Added a very rough example in the attached workbook in sheets JoblList and Cal.


Now you have entered all tasks as if you wrote them down on a paper calendar with some highlights. With VBA it should be possible to evaluate every cell in the master for its colour and copy/paste its content to the same cell in the correct Unit sheet, but that's not my kind of thing, to be honest.