VBA Split Workbook macro

[jakester]

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

[Bob Phillips]

With Activeworkbook

For i = 1 To .Worksheets.Count - 1

.Worksheets.Copy
Activeworkbook.SaveAs .Worksheets(1).Range("B5").Value
ACtiveworkbook.Close
Next i
End With

[jakester]

thanks for the response and the code.

I'm having a problem with the code, though. It's only saving the 1st worksheet and is not looping through to the 2nd worksheet to split it out and save it.

Here's the code I am using. I modified it a little to include where I wanted to save the files to:


Sub SplitWorkbook()
With ActiveWorkbook

For i = 1 To .Worksheets.Count - 1

.Worksheets.Copy

ChDir _
"C:\Documents and Settings\jbeliv1\Desktop"

ActiveWorkbook.SaveAs .Worksheets(1).Range("B5").Value

ActiveWorkbook.Close

Next i

End With

End Sub

[lucas]

Moved to the Excel help forum

Announcements would not be the appropriate place to ask questions so I have moved your thread.

[Bob Phillips]

My bad, try this



Sub SplitWorkbook()
Dim i As Long
With ActiveWorkbook

For i = 1 To .Worksheets.Count - 1

.Worksheets(i).Copy

ChDir "C:\Documents and Settings\jbeliv1\Desktop"

ActiveWorkbook.SaveAs ActiveWorkbook.Worksheets(1).Range("B5").Value

ActiveWorkbook.Close
Next i
End With

End Sub

[jakester]

My bad, try this



Sub SplitWorkbook()
Dim i As Long
With ActiveWorkbook

For i = 1 To .Worksheets.Count - 1

.Worksheets(i).Copy

ChDir "C:\Documents and Settings\jbeliv1\Desktop"

ActiveWorkbook.SaveAs ActiveWorkbook.Worksheets(1).Range("B5").Value

ActiveWorkbook.Close
Next i
End With

End Sub


This is exactly what I was looking for. ?Te pasaste!