NEED HELP! Macro loop

[Bishop]

I need help writing a macro. I recorded a macro (posted below) to show you
what I'm doing. The problem is I need it to repeat this process every 8
rows. Here's my starting point:

Sub TallySheetRepDump()
'
' TallySheetRepDump Macro
' Macro recorded 3/31/2009 by Osiris
'

'
Sheets("Catalyst Dump").Select
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A2:F6").Select
Selection.Copy
Sheets("Tally Sheet").Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Catalyst Dump").Select
Range("G2:Q6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tally Sheet").Select
Range("N6").Select
ActiveSheet.Paste
End Sub

The "Catalyst Dump" sheet contains several different reps with each rep
having between 20 and 300 transactions (or rows per rep). It changes every
week so one week I may have 10 reps and the next week I may have 40 reps. So
the first part is me sorting "Catalyst Dump" 1st by rep then by transaction
amount. I'm only interested in the 5 highest transactions for each rep so I
take the top 5 rows in the first 6 columns of "Catalyst dump" and copy them.
I then move to the "Tally Sheet" sheet and paste that info starting at cell
A6. I go back to "Catalyst Dump" and copy the remaining columns of
information (same top 5 rows) and I move that over to the "Tally Sheet"
sheet. Paste that info in starting at cell N6.

Now here's the tricky part. I need to repeat this same process for as many
reps as I have data for in "Catalyst Dump." So if I have 10 reps in
"Catalyst Dump" then I need to repeat the process 10 times. Each time the
reps data is placed in "Tally Sheet" starting at every 8th row. In other
words, in this first part you see above, the macro takes the first reps info
from "Catalyst Sheet" and dumps in rows 6-10 in "Tally Sheet". Then I need
to move to the second rep in "Catalyst Dump" and copy that reps info to
"Tally Sheet" in rows 14-18. Then take the third reps info from "Catalyst
Dump" and paste that in "Tally Sheet" rows 22-26. So on and so forth and
until the last rep in "Catalyst Dump".

I know this require some "coding" to accomplish which is why I need help.
After copying the top 5 rows for a rep I don't know how to delete the
remaining rows for that rep and move to the next rep. Nor do I know how to
make the following reps data paste every 8th row in "Tally Sheet".

Thanks in advance for you help. This is for a project and my deadline is
fast approaching.

 

[joel]

I written code like this lots of times before. I didn't test but it should
work

Sub TallySheetRepDump()
Newrow = 2
With Sheets("Catalyst Dump")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Rows("2:" & LastRow).Sort _
Key1:=.Range("B2"), _
Order1:=xlAscending, _
Key2:=.Range("F2"), _
Order2:=xlAscending, _
Header:=xlNo

Start = RowCount
For RowCount = 2 To LastRow
If .Range("B" & RowCount) <> .Range("B" & (RowCount + 1)) Then
If RowCount - Start < 6 Then
.Rows(Start & ":" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Rows(Newrow)
Newrow = Newrow + (RowCount - Start) + 1
Else
.Rows(Start & ":" & (Start + 5)).Copy _
Destination:=Sheets("Tally Sheet").Rows(Newrow)
Newrow = Newrow + 6
End If
Start = RowCount + 1
End If
Next RowCount
End With
End Sub

 

[joel]

I read your posting again and discovered I missed a couple of items.
1) Didn't start on Row 6
2) I didn't skip 8 rows, only rows copied
3) I copied the entire row instead of A:f and G:Q

Sub TallySheetRepDump()
NewRow = 6
With Sheets("Catalyst Dump")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Rows("2:" & LastRow).Sort _
Key1:=.Range("B2"), _
Order1:=xlAscending, _
Key2:=.Range("F2"), _
Order2:=xlAscending, _
Header:=xlNo

Start = RowCount
For RowCount = 2 To LastRow
If .Range("B" & RowCount) <> .Range("B" & (RowCount + 1)) Then
If RowCount - Start < 6 Then
.Range("A" & Start & ":F" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & NewRow)
.Range("G" & Start & ":Q" & RowCount).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & NewRow)
Else
.Rows(Start & ":" & (Start + 5)).Copy _
Destination:=Sheets("Tally Sheet").Rows(NewRow)
End If
NewRow = NewRow + 8
Start = RowCount + 1
End If
Next RowCount
End With
End Sub

 

[Rick Rothstein]

Does the following macro do what you want? First, though, set the parameters
in the four Const statements so they match your actual sheet layout. Also
note that I changed Order2 to xlDescending in the Sort statement as you said
you wanted the "5 highest transactions" copied to the Tally Sheet.

Sub TallySheetRepDump()
Dim X As Long
Dim CopyRow As Long
Dim LastRow As Long
Dim Rep As Range
Dim Tally As Worksheet

Const CatalystStartRow As Long = 2
Const RepNameCol As String = "B"
Const TransAmountCol As String = "F"
Const CopyStartRow As Long = 6

Set Tally = Worksheets("Sheet3") 'Tally Sheet")
With Sheets("Sheet1") 'Catalyst Dump")
.Cells.Sort Key1:=Range(RepNameCol & CatalystStartRow), _
Order1:=xlAscending, _
Key2:=Range(TransAmountCol & CatalystStartRow), _
Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
LastRow = .Cells(.Rows.Count, RepNameCol).End(xlUp).Row
Set Rep = .Cells(CatalystStartRow, RepNameCol)
CopyRow = CopyStartRow
Do
Rep.Resize(5, 6).Copy Tally.Range("A" & CopyRow)
Rep.Offset(, 6).Resize(5, 11).Copy Tally.Range("N" & CopyRow)
CopyRow = CopyRow + 8
For X = Rep.Offset(5).Row To LastRow + 1
If .Cells(X, RepNameCol).Value <> Rep.Value Then
If X > LastRow Then Exit Do
Set Rep = .Cells(X, RepNameCol)
Exit For
End If
Next
Loop
End With
End Sub

 

[Bishop]

I'm getting an error "mismatch type" here. Any idea why?

Else
.Rows(Start & ":" & (Start + 5)).Copy _
Destination:=Sheets("Tally Sheet").Rows(NewRow)

 

[Bishop]

Almost! I'm getting a "runtime error 1004: Method 'Range' of object
'_Global' failed". Here are the only changes I made:

Const RepNameCol As String = "B"
to
Const Name As String = "Name"
(because the header name for column B in the Catalyst Dump sheet is called
"Name")

Const TransAmountCol As String = "F"
to
Const TransactionAmount As String = "Transaction Amount"
(because the header name for column F in the Catalyst Dump sheet is called
"Transaction Amount")

Of course I switched all occurences of the change in the rest of the code.
I ran it after these two changes and it ran fine. The only thing was that
because order2 is descending it pasted the 5 lowest transactions to Tally
Sheet instead of the 5 highest. Also, the data copied to Tally Sheet was
shifted one column to the left because Column A in Catalyst Dump wasn't
copied. It starts copying at column B for some reason. But the "loop"
itself worked. I just need to get the 5 highest transactions and Column A
(from Catalyst Dump) included in the copy/paste. Column A in Catalyst Dump
is called "ATTUID".

BTW, I changed order2 to Ascending and still got the same error.

Bishop

 

[Matt]

I'm getting an error "mismatch type" here.  Any idea why?

Else
                .Rows(Start & ":" & (Start + 5)).Copy _
                   Destination:=Sheets("Tally Sheet").Rows(NewRow)








- Show quoted text -

I think if you change the offset in the for loop from

For X = Rep.Offset(5).Row To LastRow + 1

to

For X = Rep.offset(1).Row to LastRow + 1

The macro will work better.

(of course the name won't change on the first interation but who
cares, once it does, we'll
grab the row)

I think the Rep.offset(5).Row may go beyond the end of the data
and then it sets Rep = .Cells(x,"A") which might be a blank string ""
and the loop will go on forever.
HTH

 

[Matt]

Almost!  I'm getting a "runtime error 1004: Method 'Range' of object
'_Global' failed".  Here are the only changes I made:

Const RepNameCol As String = "B"
to
Const Name As String = "Name"
(because the header name for column B in the Catalyst Dump sheet is called
"Name")

Const TransAmountCol As String = "F"
to
Const TransactionAmount As String = "Transaction Amount"
(because the header name for column F in the Catalyst Dump sheet is called
"Transaction Amount")

Of course I switched all occurences of the change in the rest of the code..  
I ran it after these two changes and it ran fine.  The only thing was that
because order2 is descending it pasted the 5 lowest transactions to Tally
Sheet instead of the 5 highest.  Also, the data copied to Tally Sheet was
shifted one column to the left because Column A in Catalyst Dump wasn't
copied.  It starts copying at column B for some reason.  But the "loop"
itself worked.  I just need to get the 5 highest transactions and Column A
(from Catalyst Dump) included in the copy/paste.  Column A in Catalyst Dump
is called "ATTUID".

BTW, I changed order2 to Ascending and still got the same error.

Bishop








- Show quoted text -

The names have to be Excel column names,.such as "A", "B" (example:
Range("B2")) so "B" would work but "Name" would not work. The sort
uses the Header:=xlGuess option, so header names should sort
correctly.
HTH