Ms Office 2007: Splitting an Excel spreadsheet into many sheets

[SteveB_NI]

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.

 

Thanks in advance!

[ramblingricky]

Hi Steve,

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.

 

Cheers,

Richard