B
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.
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.