Ms Office 2007: Splitting an Excel spreadsheet into many sheets
Posted this also in the How to do it section but hoping I'll get a response here in the proper place!
The accounts dept here have asked would I have a solution for them to make their petty cash summary a bit easier to maintain. At present everything goes into 1 big Excel spreadsheet & this as you can imagine gets very big during the term.
I was wondering if there was a way of taking 1 large Excel spreadsheet (essentially, where the data is inputted initially) which then splits it into other sheets based on the Dept / Category name ... ie. Art dept expenses are in a separate sheet from Admin dept etc.
The only way I've found so far is to manually put the data into individual depts then have it referencing back to a summary sheet but they would rather put data into a summary page first.
I'm considering designing a MySQL database with a PHP front end to make this work - as I know it's definitely possible within that.
Here's link to a VBA script which takes stuff from a Master sheet and pastes it into individual sheets based on the value in a particular column (in your case you'd use the column listing your Departments); Sheet1 to WBs - Jerry Beaucaire's - Excel Assistant
The script was written by Jerry Beaucaire, so, as you'd expect, does exactly what it says on the tin with a minimum of fuss.
You might need to re-arrange your data a bit to make it work. But once you've done that I find it works like a dream.