Split and Save as a seprate excel file. How to do it?

Just struck me – I should have provided the sheet names that I want to include. They are East, West, North and South. They have the data for all the sales team. I want to extract these tabs and send them daily.

Once again: My book has 16 worksheets 12 are named by month (Jan/Feb/March…Dec). The other 4 are East, West, North and South. I want to create a separate workbook to include only these 4 sheets.

Thank you.

Hi simplyxl

What you require is rather straight forward. However, for me, I'd want to test the Code before I presented it to you. To do so, I'd need to build a Workbook that emulates what you're working with. You have that workbook. Please sanitize it for proprietary information and share it so I/We don't have to build it.

Hmmm...I do not have the workbook at hand. Can get one in sometime. Currently facing some server issues in the office. The data is getting migrated, unfortunately for me. I will need sometime to get that out to you. Just want to check the number of columns each data sheet has, as I don't want to give you a wrong sample.

But I am sure that the workbook has 16 tabs and I need only the ones named as East, West, North and South.

Hi simplyxl

I'd not be concerned re: this

Just want to check the number of columns each data sheet has

The Code will figure that out. Post a sample that represents what you have...explain what you want...the Code will do the rest.

Hi simplyxl

The Code in the attached appears to do as you describe. Place the attached workbook in the same Folder as the equivalent of your "Book1" workbook. Run from the Button on "Macro Book". The Code will ask you which file (again, the equivalent of "Book 1") you wish to process.

The Code assumes the Report Date is the same for East, West, North and South. It further assumes that at least one of these four Worksheets is the first Worksheet in the Workbook and it assumes that these four Worksheets exist in the Workbook.

If Daily Sales Stats - Financial Year - 2013-2014 - June 3, 2013.xlsx already exists the Code will ask if you want to delete it. If you answer Yes, the Workbook is deleted and recreated. If you answer No, the Code terminates.

This "Stuff" complicated things significantly:

Create a fresh report with the name as: Daily Sales Stats - Financial Year - 2013-2014 - (Input Date) 2013_6_3 = 6/3/2013
In column A of the fresh report update the date from Column A of Main Report (Book 1) 2013_6_3 = 6/3/2013
In column B of the fresh report update the Name from Column A Brad Fred.xls = Brad Fred

Try it, let me know of issues.

Hi simplyxl

No, this is not an issue

there were less than 16 tabs only till June

THIS is an issue

The Code assumes the Report Date is the same for East, West, North and South. It further assumes that at least one of these four Worksheets is the first Worksheet in the Workbook and it assumes that these four Worksheets exist in the Workbook.

What this Code snippet is doing is parsing out the Date

Please Login or Register  to view this content.

This error typically means that something does not exist

Run Time Error 9

I don't get that error with your sample "Book 1"...so, there apparently is some difference(s) in the Book you actually ran the Code against. If you've not done so, run against "Book 1" and let me know what happens.

Another thing I'd like you to do...do you know how to set a break point in the Code? Open and use the Immediate Window? If yes, set a break point at that line of Code. Then, open the immediate window and type in ?.range("A1")...what do you see? On your sample Book 1 I see 2013_6_3.

If you're not familiar with the above process let me know, I'll step you through it.

Hi simplyxl

Let's geter running...then we'll figure out a way to incorporate

can I include this as a module in my existing macro

Good idea!

I found a code before posting here on this link: http://www.extendoffice.com/document...-workbook.html

It was splitting the worksheets into workbooks for me but I want to get the data from the 1 to 4 zones into one workbook with the explanation incorporated in my Book 1 I shared.

I also see that the number of columns in the 4 (East - South) tabs vary. I am guessing that is not a concern. Though my sample had the same number of columns in each sheet. Is this right?

Hi simplyxl

Have you run the Code on the sample file you posted (Book 1)? Did you get errors?

Hi simplyxl

I can't duplicate the error. Please upload the Workbook you're running the Code against. I may have inadvertently modified something.

Hi simplyxl

May I also have your permission to sign off for a bit

You don't need my permission!!! Do you job! I'll continue to help as I can! Gotta' admit...at this point, I don't know why it works for me and not for you.

Hi simplyxl

Well, now I know why you were getting this error

Run time 9 on cel.Offset(0, 1).Value = Split(cel.Value, "-")(1)

In attached Macro Book I've cleared some variables. I don't believe this change was causing an issue...just to be on the safe side.

Place both these files in the same Folder...don't care where. Run the Code...let me know...

When it works with Book 2...I'll explain...

Hi John,

This works great even with my original data. Apart from one error (point 1).

Few things that require your expertise further:


1 - I get an error on this line:

Please Login or Register  to view this content.

This is because I found out that the name of the path was longer in today's version maybe this was created by someone else at a different location. The macro (real data sheet I got). So I tried amending the line as below:

Please Login or Register  to view this content.

But it did not work, as I expected in my current state...

The original final name in Column A is like this:

Please Login or Register  to view this content.

Can this be made dynamic?


2 - Can an auto adjustment to the column widths be added?

3 - I also understood from your notes that the code assumes that first sheet to be any one of East/West/North/South. So it will not work if these are not. Is it possible to change it so as to copy irrespective of the placement of the sheets?

4 - Also it doesn’t work when any of the 4 sheets don’t have a data. For instance, the report today did not have data in south sheet. I got a runtime error on

Please Login or Register  to view this content.

5 - Once this is done, I can run the test again and then we can look into the integration of this as a module into the existing code file that my office sends this collated data in, as you had previously suggested.

Thank you for the time and expertise!

Rest all is great!

Hi simplyxl

I can make the Code work on records like this

C:\Users\ASRAR\Desktop\Sales Report\Revenue Reports\ FORECASTING - SaleSMC - South Report - Sale Revenue Report - 2013_6_3 - Brad Fred.xls

However, unless you standardize your Naming Convention, inevitably you'll be faced with errors.

A way to reduce the potential for error would be for the User select a Report Date from a popup calendar.

I'll need to look at these other issues:

• Can an auto adjustment to the column widths be added? This is already being done...please explain further...
• Also it doesn’t work when any of the 4 sheets don’t have a data. I'll look at this...it can be fixed.
• Is it possible to change it so as to copy irrespective of the placement of the sheets? Can be done...will take a bit more Code...if we go with the popup calendar this issue goes away.

Get back to me...I'll get back to you.

Hi simplyxl

The Code in the attached accommodates these things:

Popup calendar

Can an auto adjustment to the column widths be added

it doesn’t work when any of the 4 sheets don’t have a data

change it so as to copy irrespective of the placement of the sheets

Let me know of issues.

Hi simplyxl

Tell me the process, step by step...perhaps I can figure it out just from your description but, probably not. I'd really like to see the Workbook (preferably populated with Data) and the Code.

If this is not possible I can explain how to migrate Macro Book Code and frmCalendar to your compiler workbook but the integration will probably get messy if I can't "see" what's going on.

How would you want the Macro Book Code to run? As a separate, final step by pushing a button or integrated with the compiler Code with no User interaction?

Hi simplyxl

I understand

sorry for my inability to share the preparatory code

Let me play with it...I'll describe what I believe I would do. This will probably not work the first time around...we may need several runs at it.

Hi simplyxl

I'm not certain how I'll approach this yet...
Will you be able to add a Button in the compiler?
Will you be able to add a worksheet?
Can you give me a Cell Address on a worksheet that's ALL mine? If so, I'll need the Sheet Name and the Cell Address

Will you be able to add a Button in the compiler? - Yes

Will you be able to add a worksheet? If mandatory "Yes"

Can you give me a Cell Address on a worksheet that's ALL mine? - in case of new "A1", "A15" in case of existing

If so, I'll need the Sheet Name and the Cell Address - "Split and Save" in case of new. Old one is I guess Compiler where I want to add a button

So, you'll add a Worksheet called "Split and Save". This will be a blank Worksheet except it will have the "Run Me" Button...is this correct?

Hi simplyxl

EDIT: Upper Right References should be UPPER LEFT but then, I'm sure you figured that out.

I can't/won't promise this'll work.

1. Create a new Folder on your Desktop (or anywhere...just know where it is) and call the Folder "Macro Code"
2. Save the new attached Macro Book With Calendar v2 in this new Folder
3. Open Macro Book With Calendar v2 and go into VBA (Alt + F11)...you'll see a screen similar to this...make certain Forms, Modules and Class Modules are expanded.
4. Alt_F11 Screen.jpg
5. Highlight frmCalendar...click on File (upper right)...click Export File...navigate to the "Macro Book" Folder...click Save
6. Highlight CreateBookMod...click on File (upper right)...click Export File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...click Save
7. Highlight mGlodals...click on File (upper right)...click Export File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...click Save
8. Highlight clsCmdButtons...click on File (upper right)...click Export File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...click Save
9. Having done this, all the Macro Book Code and UserForm are in your Folder "Macro Code"
10. Close Macro Book With Calendar v2
11. IMPORTANT!!! Create a COPY of your Compiler Workbook and open it
12. Add a Worksheet called "Split and Save". If you CHANGE the Name of this Worksheet the Code will also need changed...this Worksheet is referenced by Name in the Code.
13. Go into VBA (Alt + F11)...I can't give you a picture because I don't have the File...you'll see a screen similar to the previous jpg except it'll be the compiler project stuff.
14. Click on File (upper right)...click Import File...navigate to the "Macro Book" Folder...select frmCalendar...click Open
15. Click on File (upper right)...click Import File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...select CreateBookMod...click Open
16. Click on File (upper right)...click Import File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...select mGlodals...click Open
17. Click on File (upper right)...click Import File...you won't have to navigate this time but if you do, navigate to the "Macro Book" Folder...select clsCmdButtons...click Open
18. Having done this, all the Macro Book Code and UserForm are in your compiler Workbook.
19. Select the Tab "Split and Save"...add a Forms Control Button to this worksheet...call it what you wish. Assign the Macro Create_Book to this Button.

As you know, I can't test this process...I performed all the above steps and it appears to work...I'll help as I can, how I can...let me know of issues.

EDIT: Upper Right References should be UPPER LEFT but then, I'm sure you figured that out.

Thank you for this detailed explanation John.

I followed all the steps suggested. But on running the Save and Split "Create Work Book" module it is asking me to open another file as was with V1, when I try to open the file it says that the file is already open. Any thoughts...

I understand you have no access to my original file is posing this, as you were able to run. Maybe we can give it a shot or two else I'll check with the boss if I can share the original file. Sorry for this but its beyond my authority

Thanks for all the help.

Hi simplyxl

Well you've got me confused...where do Sheets East, West, North and South live? Tell me the process, step by step...

Hi simplyxl

I misunderstood...I thought the compiler Code was creating a new Workbook.
Replace ALL Code in Module CreateNewBook with this Code...let me know of issues.

Please Login or Register  to view this content.

Hi simplyxl

You're welcome...glad I could help. Thanks for the Rep.

This can be done...

auto run both the codes at a certain time on a daily basis

If you want the Code to run without User Intervention the Code would need to be revised for those messages that require User Input, such as Select Report Date and Delete and Recreate File. Report Date could be put in as a Default to Today's Date (if that works for you) and Delete and Recreate File could be eliminated and just default to Kill the File.

Think about what you'd like to happen and when; then I'd suggest you start a New Thread for this issue.