VBA Loop help

[Meinthecorner]

I have a sheet (AgentSummary) that lets the user cycle through a number of employee giving a score for various performance areas. There scores give a balanced score for the week. On a seperate sheet (WeeklySummary) the employees are all listed by row and week numbers across the top.

To save having to go through each record on the AgentSummary sheet, copy that employees weekly performance row and paste that to a new row on the WeeklySummary sheet, I cobbled together a basic loop shown below.

Its the 2nd time Ive used a loop, so the vba could probably be improved.

My question is, when the loop goes to the Weeklysummary sheet to paste the data over, how do I get it to start pasting it from row 6 and not row 2?

Sub CreateCard()


 Application.ScreenUpdating = False

    Sheets("WeeklySummary").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A1").Select
    Sheets("AgentSummary").Select
 

    Range("f160").Select
    ActiveCell.FormulaR1C1 = "0"
    
Do

AgentNumber = 1 + AgentNumber
For Each cell In Range("f160")

cell.Value = cell.Value + 1

    Range("J161:BO161").Select
    Selection.Copy
    
 Sheets("WeeklySummary").Range("a200").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Sheets("AgentSummary").Select

Next cell

Loop While AgentNumber < 63
Sheets("AgentSummary").Select
    Range("f160").Select
        ActiveCell.FormulaR1C1 = "6"
        
   Application.ScreenUpdating = True
End Sub

 

[Meinthecorner]

I've now worked out a way round this. I've changed this part

    Sheets("WeeklySummary").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.ClearContents

To a range starting from cells.select to a Range starting from A7:BH300, with the column headers in Row 6.

 

[NickHa]

You need to put a dummy value in cell A5 in WeeklySummary, to allow the offset to locate to A6 first time throught the loop. So add

Sheets("WeeklySummary").Range("A5") = "x"

just before the outer loop starts, then add

Sheets("WeeklySummary").Range("A5").Clear

after the loop ends. This isn't the neatest solution, but it works!

 

[Meinthecorner]

Thanks for the reply. I needed static column headers, so they are now acting as the dummy values.