Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!

Why not just use datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steve" wrote in message
ups.com...
Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!

Hi Don. Because the newly created sheets will be automatically
emailed to the business owners. Also, the sheets will be printed and
added to a monthly book that goes out.

On Aug 10, 3:35 pm, "Don Guillett" wrote:
Why not just use datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in message

ups.com...



Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted text -

- Show quoted text -

Try this. It will make the sheet if necessary and append the data to the
appropriate sheet.There should be only ONE dot if front of each with segment
such as .range, .showalldata, etc. Assign to a button or shape.

Sub CopyDaily()
Application.ScreenUpdating = False
With Sheets("Data")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each c In .Range("a2:a" & lr).SpecialCells(xlVisible)
On Error Resume Next
If Worksheets(c.Value) Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = c
End If
.ShowAllData
.Range("a1:a" & lr).AutoFilter field:=1, Criteria1:=c
dlr = Sheets(c.Value).Cells(Rows.Count, "a").End(xlUp).Row + 1
.Range("a2:a" & lr).Copy Sheets(c.Value).Range("a" & dlr)
Next c
.ShowAllData
.Range("a1:a" & lr).AutoFilter
End With
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steve" wrote in message
oups.com...
Hi Don. Because the newly created sheets will be automatically
emailed to the business owners. Also, the sheets will be printed and
added to a monthly book that goes out.

On Aug 10, 3:35 pm, "Don Guillett" wrote:
Why not just use datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in
message

ups.com...



Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted
text -

- Show quoted text -

Thanks don. The code creates the sheets, but does not copy any data
to them. Looks like the variable dlr is always "empty", and since its
part of the copy to range, never pastes.

On Aug 11, 8:13 am, "Don Guillett" wrote:
Try this. It will make the sheet if necessary and append the data to the
appropriate sheet.There should be only ONE dot if front of each with segment
such as .range, .showalldata, etc. Assign to a button or shape.

Sub CopyDaily()
Application.ScreenUpdating = False
With Sheets("Data")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each c In .Range("a2:a" & lr).SpecialCells(xlVisible)
On Error Resume Next
If Worksheets(c.Value) Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = c
End If
.ShowAllData
.Range("a1:a" & lr).AutoFilter field:=1, Criteria1:=c
dlr = Sheets(c.Value).Cells(Rows.Count, "a").End(xlUp).Row + 1
.Range("a2:a" & lr).Copy Sheets(c.Value).Range("a" & dlr)
Next c
.ShowAllData
.Range("a1:a" & lr).AutoFilter
End With
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in message

oups.com...



Hi Don. Because the newly created sheets will be automatically
emailed to the business owners. Also, the sheets will be printed and
added to a monthly book that goes out.

On Aug 10, 3:35 pm, "Don Guillett" wrote:
Why not just use datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in
message

roups.com...

Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted
text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I just re-tested and it worked,as advertised, to append to the next
available row on each sheet. IF? you only want to do this ONCE then change
to
.Range("a2:a" & lr).Copy Sheets(c.Value).Range("a1")

I am sending you my workbook. Delete all sheets except DATA and try a couple
of times.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steve" wrote in message
ups.com...
Thanks don. The code creates the sheets, but does not copy any data
to them. Looks like the variable dlr is always "empty", and since its
part of the copy to range, never pastes.

On Aug 11, 8:13 am, "Don Guillett" wrote:
Try this. It will make the sheet if necessary and append the data to the
appropriate sheet.There should be only ONE dot if front of each with
segment
such as .range, .showalldata, etc. Assign to a button or shape.

Sub CopyDaily()
Application.ScreenUpdating = False
With Sheets("Data")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterInPlace,
Unique:=True
For Each c In .Range("a2:a" & lr).SpecialCells(xlVisible)
On Error Resume Next
If Worksheets(c.Value) Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = c
End If
.ShowAllData
.Range("a1:a" & lr).AutoFilter field:=1, Criteria1:=c
dlr = Sheets(c.Value).Cells(Rows.Count, "a").End(xlUp).Row + 1
.Range("a2:a" & lr).Copy Sheets(c.Value).Range("a" & dlr)
Next c
.ShowAllData
.Range("a1:a" & lr).AutoFilter
End With
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in
message

oups.com...



Hi Don. Because the newly created sheets will be automatically
emailed to the business owners. Also, the sheets will be printed and
added to a monthly book that goes out.

On Aug 10, 3:35 pm, "Don Guillett" wrote:
Why not just use datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Steve" wrote in
message

roups.com...

Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and
then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had
Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted
text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Try this code. Change Summary Sheet to match your sheet name for DATA.

Sub ParseSheets()

Const SummarySheet = "Sheet1"

Worksheets(SummarySheet).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Worksheets(SummarySheet). _
Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
If Not IsEmpty(cell) Then
If cell.Row = 1 Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Set PreviousRange = Range(Cells(1, "A"), _
Cells(cell.Row - 1, "A"))
Set c = PreviousRange.Find _
(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Worksheets(cell.Value).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
cell.EntireRow.Copy Destination:= _
Rows("$" & (LastRow + 1) & ":$" & (LastRow + 1))
End If
End If

End If
Next cell


End Sub


"Steve" wrote:

Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!

Thanks Joel, But got an error on this line:

Worksheets(cell.Value).Activate


On Aug 10, 4:02 pm, Joel wrote:
Try this code. Change Summary Sheet to match your sheet name for DATA.

Sub ParseSheets()

Const SummarySheet = "Sheet1"

Worksheets(SummarySheet).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Worksheets(SummarySheet). _
Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
If Not IsEmpty(cell) Then
If cell.Row = 1 Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Set PreviousRange = Range(Cells(1, "A"), _
Cells(cell.Row - 1, "A"))
Set c = PreviousRange.Find _
(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Worksheets(cell.Value).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
cell.EntireRow.Copy Destination:= _
Rows("$" & (LastRow + 1) & ":$" & (LastRow + 1))
End If
End If

End If
Next cell

End Sub



"Steve" wrote:
Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted text -

- Show quoted text -

I changed in two places in the code cell to cell.value. If it fails give me
the data in column A and how far it got before failing. IUt create a
worksheet with a cell name and then later it could not activate the same
worksheet it created. I tsuspect it has to do with the change I made. let
me know the results

Sub ParseSheets()

Const SummarySheet = "Sheet1"

Worksheets(SummarySheet).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Worksheets(SummarySheet). _
Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
If Not IsEmpty(cell) Then
If cell.Row = 1 Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell.value
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Set PreviousRange = Range(Cells(1, "A"), _
Cells(cell.Row - 1, "A"))
Set c = PreviousRange.Find _
(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell.value
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Worksheets(cell.Value).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
cell.EntireRow.Copy Destination:= _
Rows("$" & (LastRow + 1) & ":$" & (LastRow + 1))
End If
End If

End If
Next cell


End Sub


"Steve" wrote:

Thanks Joel, But got an error on this line:

Worksheets(cell.Value).Activate


On Aug 10, 4:02 pm, Joel wrote:
Try this code. Change Summary Sheet to match your sheet name for DATA.

Sub ParseSheets()

Const SummarySheet = "Sheet1"

Worksheets(SummarySheet).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Worksheets(SummarySheet). _
Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
If Not IsEmpty(cell) Then
If cell.Row = 1 Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Set PreviousRange = Range(Cells(1, "A"), _
Cells(cell.Row - 1, "A"))
Set c = PreviousRange.Find _
(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then
Worksheets.Add after:=Sheets(Worksheets.Count)
ActiveSheet.Name = cell
cell.EntireRow.Copy Destination:=Rows("$1:$1")
Else
Worksheets(cell.Value).Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
cell.EntireRow.Copy Destination:= _
Rows("$" & (LastRow + 1) & ":$" & (LastRow + 1))
End If
End If

End If
Next cell

End Sub



"Steve" wrote:
Hi everyone. If I have a sheet called Data, is there a way to have
vba scan all of column A, identify unique values in column A, and then
parse out the entire rows into new sheets? So for instance, if the
values in ColumnA are Blue, Green and Yellow. VBA wold create 3 new
sheets. In the Blue sheet, there would only be records that had Blue
in Column A, and so forth. The only catch is the number of unique
values in ColA can change from month to month. Thanks!- Hide quoted text -

- Show quoted text -

Hi Steve

Have a look at this file I made up recently:

http://users.skynet.be/fa436118/wim/...mtabbladen.xls

That's the direct link to the example, inclusive of the code. Modify to suit
your needs.

There's a page full of explanations on my site http://www.wimgielis.be, but
since that site is in Dutch, linking to it is probably useless in your case.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music