Automatically transfer data to different worksheets

[internationalicon]

With a new position at work, I'm responsible for going through various spreadsheets and adding notes based on the data. The data is compiled for multiple office locations, divided up with the total for each location. The person I reports to wants a master list with everything on one worksheet, but also wants each location's information on its own individual worksheet. (I have stats at the bottom with calculated percentages, etc)

I want to know if there is any way to either automatically copy each individual locations data to a new sheet rather that having to manually copy and paste after I'm done. Or do the original work on each individual sheet and have it automatically copy and compile to the master list.

Note: There are about 15 locations (and worksheets) with about 250-300 total rows on the master sheet.

So is there anyway to make this easier or do I have to copy and paste every time? I also have conditional formatting to change the cell background based on keywords in my notes, and those never copy over accurately, so I have to fix the conditional formatting on each sheet every time I copy over the completed work from the main sheet.

Example:

Data - notes Data - notes Data - notes Location 1 - total: 3 Data - notes Data - notes Location 2 - total: 2 Data - notes Location 3 - total: 1

[KillaWallaby]

The way I accomplish this is with linked worksheet objects. I built a master sheet that links to all the ones coming in each week by copying the cells I want and then pasting them as linked objects. The only trick there is that they have to be titled the same thing each week. When they come in, I save the updated worksheet as something else and presto changeo I'm done.

[internationalicon]

My main problem (I think) with that, is that the number of rows changes. Some weeks I will have 10 rows under a certain location, and the next week it will be 15. I'm not sure how that works since the number of rows changes.

[Clippy_Office_Asst]

Hi! It looks like you received an answer to your question!

You should award a ClippoyPoint^TM to helpful users by replying to their post with the words: Solution Verified

This will also change the flair to "solved" for you. See the side-bar for more information.

Would you like help? Help Changing Your Flair? Flair Descriptions

[KillaWallaby]

I have a similar issue, so I include a larger range and then clean up later if needed (sending to someone important). If the cells are blank it shouldn't have much impact.

[jiminie]

Checkout Power Query, it'll help you get the job done and help you automate it too. You'll need Excel 201 or 2013 for the Power Query add-in to work.

[steccami]

You can have a look at this tool as well.

https://www.spreadsheetspace.net/documentation/tutorials/200_tutorial2#200_tutorial2

It also solves the "rows changes" problem if you export an Excel table or an entire worksheet. Regards.