Copy/Paste Macro using large amount of Data
 

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

Copy/Paste Macro using large amount of Data
 

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

Copy/Paste Macro using large amount of Data
 

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