jakester
09-30-2008, 07:45 AM
hello -
I am trying to create a macro that will split a workbook into separate excel files. My version of Excel is Office 2003 if that matters.
Every month I have to run a report that tells me how many accounts I have in a given market. The report then exports all of the data into excel and splits the markets into separate worksheets. Now, the number of worksheets is variable so one month I could have 6 worksheets in the workbook or 11 worksheets in the workbook. The last worksheet is always a non-essential one that just contains the details of the original query I ran.
Cell B5 of each worksheet contains the name of the market that I would like to use in naming each worksheet that I will be splitting out into a separate excel file.
Let me try to illustrate this to help make my problem clearer. I ran a report and it exported all of the data into excel for me. It has 11 worksheets in it and the last worksheet is just the details of the query. In cell B5 of worksheet 1, it says RI-Warwick. I want to split out that worksheet with all of its data from the workbook and then create an excel file titled: RI-Warwick.xls. Cell B5 of the worksheet 2 says MA-Boston. Again, I want to split that out into a separate excel file and name it MA-Boston.xls. I want to continue splitting out the remaining worksheets until I get to the last one?I would ignore this worksheet altogether and end the macro on the last worksheet (or end of the loop).
Let me know if you need any additional information.
Thanks for your help and expertise.
Jake
I am trying to create a macro that will split a workbook into separate excel files. My version of Excel is Office 2003 if that matters.
Every month I have to run a report that tells me how many accounts I have in a given market. The report then exports all of the data into excel and splits the markets into separate worksheets. Now, the number of worksheets is variable so one month I could have 6 worksheets in the workbook or 11 worksheets in the workbook. The last worksheet is always a non-essential one that just contains the details of the original query I ran.
Cell B5 of each worksheet contains the name of the market that I would like to use in naming each worksheet that I will be splitting out into a separate excel file.
Let me try to illustrate this to help make my problem clearer. I ran a report and it exported all of the data into excel for me. It has 11 worksheets in it and the last worksheet is just the details of the query. In cell B5 of worksheet 1, it says RI-Warwick. I want to split out that worksheet with all of its data from the workbook and then create an excel file titled: RI-Warwick.xls. Cell B5 of the worksheet 2 says MA-Boston. Again, I want to split that out into a separate excel file and name it MA-Boston.xls. I want to continue splitting out the remaining worksheets until I get to the last one?I would ignore this worksheet altogether and end the macro on the last worksheet (or end of the loop).
Let me know if you need any additional information.
Thanks for your help and expertise.
Jake