save Active tab to new workbooks based on value in column

Hi Guys

Some time ago one of the excellent coding guru's here helped me out with a neat macro that would save the active worksheet to a new workbook keeping the original filename and appending the worksheet tab name.

However I was wondering if this macro could be updated to check for values in column B and when the department name changes save them to separate workbooks. Each department is seperated by a blank row.

For example if I had a Workbook called 'MyWorkbook.xls' with a tab called 'MyWorkSheet' then in column B2:B3 'Department A' and in B5:B13 had 'Department B' then the macro would scan column B and save 2 new works sheets called 'MyWorkbook - MyWorkSheet-Department A.xls' and 'MyWorkbook - MyWorkSheet-Department B.xls'

I have included the original code below and I wondered if this could be updated and behaps using the blank rows between departments to let the code know a save should be actioned.

I would be grateful for any assistance.

Thanks.

WorkBook name: 'MyWorkBook.xls'

Sub SaveActiveSht()
    Dim myPathWhereToSave As String
    Dim wbNm   As String
    myPathWhereToSave = "C:\MyFileLocation\"

    wbNm = myPathWhereToSave & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=wbNm & " - " & ActiveSheet.Name, _
                          FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                          ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveWorkbook.Close

    Application.ScreenUpdating = True

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Please remove macro from your Title

@royUK

Apologies for my title, I tried to convey what I wanted in my title whle trying to make it concise. As 2 days have passed I guess I will have to ask a Mod to edit the title for me.

Thanks.

@ravishankar

Thank you so much for the code provided.

Unfortunately the 1st code did not work as I receive a compile error: 'For without Next', it doesn't matter where I put the Next command I still receive the error.

The second piece of code does not work for me either. I get a compile error: 'Named argument not found' at this point: 'FileFormat:='

I have literrally copy and pasted your code and just amended the file path to where i want to save it locally.

I notice the second piece of code doesn't have a Next either. Is placing a Next after a For command optional in VBA? Sorry for my ignorance I am a noob when it comes to VBA.

Thanks.

Hi All

I have added a sample spreadsheet to show what I require. As you can see Column B has the various Departments within the Faculty.

In the attached example the Macro should create 3 new Workbooks:

MyWorkbook - MyWorkSheet - Procurement Services.xls containing all data from A2:J5

MyWorkbook - MyWorkSheet - Financial Accounting.xls containing all data from A7:J14

MyWorkbook - MyWorkSheet - Financial Systems.xls containing all data from A16:J18

Also all headers from A1:J1 should be present on the new sheets.

I hope this makes sense.

Hi Ravi

Thanks for the code, I appreciate it what you have done. However there are still a couple of issues. I think maybe I might not have explained myself. When I said MyWorkSheet I just used that as an example.

The Worksheet can have any number of name depending on Faculy hence why the original macro saved and appended using the activesheet code and the new documents created have the same name as the original.

I have posted a sample of how the newly created docs should look. Also the Workbook itself could be called a number of names so it would also need to be dynamic dependant on what the actual workbook is named.

Sorry to be so picky it's just I have a lot of departments to sort and it helps me know what to send to whom.

Thanks again for any additional help you can provide.

Hi Ravi

Thanks for the addtional code. It seems to work ok. I was wondering if it was possible to name the new sheets to match the name of the original sheet whatever that may be. In this case it was 'testsheet'?

Also in column K another Department field has shown up, is this needed?

Thanks.

Hi Ravi

Thanks for the new code, I wanted to copy it to my personal.xls so that it is available to all my open sheets. However i get a compile error at the line: 'For a = 2' with an error 'Compile error: Variable not defined'.

What can I define it as? A range?