VBA Code to extract and save sections of worksheet to seperate files

Hi Guys,

I have this spreadsheet that I have to use for monthly biling of all our departments and email off the relevant sections to their respective departments.

What I need is a code that can copy the section I need and paste this information into a new seperate workbook and then save that workbook under the cost centre name which can be found in Column J. I also need to make sure Rows 1 to 3 are kept when seperating the different departments as these contain the column headers.

Is it possible to keep the borders also? The only way I can think of finding where the different departments end is by the Bold section in between columns M to S. Maybe if the Macro could search for this bold line and copy up to this section and then it know the next line will be a new cost centre. I only need columns A to U to to be put in new workbook but it doesn't matter if the rest of the columns are kept also.


I have attached the spreadsheet in quesion so you can look at it. On Sheet2 I have made an example of what I want done as I have copied and pasted a section out to show what I need, although the code would need to save this to a seperate workbook.

How easy would it be to acheive this?

Thanks in advance.

Are you restricted to that format of sheet? I would lay out the data in a table so that PivotTables could be used.

Hi RoyUK,

Yes I am somewhat restricted to that format as this is the way it is extracted from the database and I don't have any control over that.

So basically a Pivot Table would be hard to do in this case.

Any ideas on how I can resolve this?

Thanks.

It looks as though this is quite a complex procedure as I suspected and probably to beyond of the scope of free help bearing in mind the time it would take to try and write a lengthy code for.

However I appreciate you trying to help me out RoyUK and thank you for taking the time to look at my problem.

I guess I need to learn more VBA and come back to the drawing board.

Thanks.

I will try to take a look at it but I am rather busy at the moment.

Hi RoyUK, I would appreciate that. I know it is rather a complicated task and I appreciate this is a big ask and to figure out a solution when you have more important work to do is not easy.

Maybe when you some spare time, if you could look at it then I would very much appreciate it but if it is to complicated I will just have to stick to the good ol' Copy and Paste.

Thanks again.