Hi All:
Can you please tell me if there is a macro that will copy at each change in
Customer name and paste into a new worksheet. For example, if I have over
32,000 lines of data each subtotalled by Customer Name and Sales $, how can I
get a macro to copy each group and paste into a new worksheet. I have about
60 different customers so wanted to try to reduce some of the manual copy and
paste that is necessary.

Thanks to all for your response.

Nikki

Hi Nikki,

You don't provide too much information but here's a start. Right click the
sheet tab of the sheet with your data in, view code and paste the code below
in.

It looks for a change in the name in column A and will copy the data to
sheet 2

Sub stance()
Dim MyRange
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If UCase(c.Value) < UCase(c.Offset(1).Value) Then

If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

Mike

"Nikki" wrote:

Hi All:
Can you please tell me if there is a macro that will copy at each change in
Customer name and paste into a new worksheet. For example, if I have over
32,000 lines of data each subtotalled by Customer Name and Sales $, how can I
get a macro to copy each group and paste into a new worksheet. I have about
60 different customers so wanted to try to reduce some of the manual copy and
paste that is necessary.

Thanks to all for your response.

Nikki

Mike:
I will try this.
I have headers such as - Customer name, Sales$, and Sales Rep. I have sorted
by the highest sales $ first, and then subtotaled each Sales Rep. Now my task
is to copy the customer information by Sales Rep and paste it into a new
individual worksheet by Sales Rep. Make sense? I will try the code that you
gave me but please let me know if this will work.

Thanks agian.

Nikki

"Mike H" wrote:

Hi Nikki,

You don't provide too much information but here's a start. Right click the
sheet tab of the sheet with your data in, view code and paste the code below
in.

It looks for a change in the name in column A and will copy the data to
sheet 2

Sub stance()
Dim MyRange
Dim copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If UCase(c.Value) < UCase(c.Offset(1).Value) Then

If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

Mike

"Nikki" wrote:

Hi All:
Can you please tell me if there is a macro that will copy at each change in
Customer name and paste into a new worksheet. For example, if I have over
32,000 lines of data each subtotalled by Customer Name and Sales $, how can I
get a macro to copy each group and paste into a new worksheet. I have about
60 different customers so wanted to try to reduce some of the manual copy and
paste that is necessary.

Thanks to all for your response.

Nikki