Create Multiple Sheets

[Guest]

I know the hot topic is merging multiple sheets into one... I'm trying to
unmerge data. I have spreadsheets that repeat data. So basically I have a
date, the next row contains all the headers. Then the data... A space then
another date headers and its data, so on and so on...

The data can vary, for example a sheet will have data like this
03/01/06 - A1, Headers A2, Data A3: AA45,
03/02/06 - A47, headers A48, Data A49: AA59

The two things that are constant the headers length, they will always go
from A3:AA45 and there is always one space between the end of the data and
the beginning of the next set.

I would like to create a macro that grabs the date and all its data to the
next space and places it in its own sheet and continue doing it until no more
data is left in the original sheet... So I would end up with 47 sheets for
example. Please help. Thanks

Ramon

 

[Guest]

Assume all the dates are unique:

Sub DisperseData()
Dim rng as Range, ar as Range
Dim sh as Worksheet
with worksheets("Master")
set rng = columns(1).specialcells(xlconstants)
End with
for each ar in rng.Areas
set sh = worksheets.Add After:=worksheets(worksheets.count)
sh.name = ar(1).Text
ar.entirerow.copy Destination:=sh.Range("A1")
Next
End Sub

 

[Guest]

Tom

I get a syntax error on
set sh = Worksheets.Add After:=Worksheets(Worksheets.count)

Any thoughts... Thanks again for the help

 

[Guest]

a last minute change. Apparently incomplete. should be

set sh = Worksheets.Add( After:=Worksheets(Worksheets.count))

 

[Guest]

Tom

We are almost there... I'm getting an Error "Application-defined" or object
defined error.... Here is what I have so far..

Sub DisperseData()
Dim rng As Range, ar As Range
Dim sh As Worksheet
With Worksheets("Master")
Set rng = Columns(1).SpecialCells(xlConstants)
End With
For Each ar In rng.Areas
Set sh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
sh.Name = ar(1).Text
ar.EntireRow.Copy Destination:=sh.Range("A1")
Next
End Sub


Thanks Again..

 

[Guest]

I found the error it was looking for text field in ar(1), I change that to,
ar(2), fixed the first group of numbers but still having problems grabbing
the second and third set. I'm still working on it. I'll let you know when I
get some where.

If you think something let me know. Thanks

 

[Guest]

Got it.... It was naming of sheets that needed to be commented. Out . I
apprecate help... It worked great... Code I used is below

Sub DisperseData()
Dim rng As Range, ar As Range
Dim sh As Worksheet
With Worksheets("Master")
Set rng = Columns(1).SpecialCells(xlConstants)
End With
For Each ar In rng.Areas
Set sh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
'sh.Name = ar(1).Number
ar.EntireRow.Copy Destination:=sh.Range("A1")
Next
End Sub

 

[Guest]

My bad - forgot that the sheetname can't have a slash in it. I was trying to
name your sheets with the date. If you want to try that, you can try


sh.Name = format(ar(1).Value,"yyyymmdd")

perhaps.