Split 1 Master Worksheet to Many
 

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 |
+-------------------------------------------------------------------+

--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=545755

Split 1 Master Worksheet to Many
 

'---------------------------------------
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
--
AP

"Vlad999" a écrit
dans le message de news:
...

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 |
+-------------------------------------------------------------------+

--
Vlad999
------------------------------------------------------------------------
Vlad999's Profile:
http://www.excelforum.com/member.php...o&userid=33586
View this thread: http://www.excelforum.com/showthread...hreadid=545755

Split 1 Master Worksheet to Many
 

Thank you thats perfec

--
Vlad99
-----------------------------------------------------------------------
Vlad999's Profile: http://www.excelforum.com/member.php...fo&userid=3358
View this thread: http://www.excelforum.com/showthread.php?threadid=54575