Split 1 Master Worksheet to Many

[Vlad999]

Ok I have one main data sheet and I am looking for a macro to split that
sheet into many spreadsheets.

Each set of data is seperated by a blank row. Data sets are variable.

I have attached 2 spreadsheets to show what i start with and what I am
trying to have when the macro runs through its process.

The "My Master Data sheet.xls" Sheet is the sheet i start with and "My
Goal.xls" is what I want the spreadsheet to look like at the end of the
process.


+-------------------------------------------------------------------+
|Filename: Help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4815 |
+-------------------------------------------------------------------+

 

[Ardus Petus]

'---------------------------------------
Sub SplitMaster()
Dim rSrc As Range
Dim wsDest As Worksheet

Set rSrc = Worksheets("Master").Range("A3")
Do While rSrc.Range("A1").Value <> ""
Set wsDest = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsDest.Name = rSrc.Range("A1").Value
Set rSrc = Range(rSrc.Range("A1"), _
rSrc.Range("A1").End(xlDown) _
.Resize(, 7) _
)
rSrc.Copy Destination:=wsDest.Range("A1")
Set rSrc = rSrc.Offset(rSrc.Rows.Count + 1)
Loop
End Sub

'---------------------------------------

You may comment out the line :
wsDest.Name = rSrc.Range("A1").Value
if you want your sheets named "Sheet2", "Sheet3", ...

HTH

 

[Vlad999]

Thank you thats perfec