Need help creating a loop please

[skyline501]

Hello,

I have a macro which steps through a process of Selecting the Dashboard, entering week number 1, going to another worksheet, copying a cell result and pasting it on another worksheet.
Then back to the Dashboard and enter week 2 and repeat for 53 weeks. Although it works fine it is clumsy and seems tailor made for a loop but it is beyond my skills
Any help would be much appreciated.
I've pasted the first week's step below:

Sub GetHours()


    Sheets("Dashboard").Select
    Range("R8").Select '(Always cell R8)
    ActiveCell.FormulaR1C1 = "1" '(Loop 1 - 53 (Week Numbers))
    Range("R8").Select '(Back to cell R8)
    Sheets("The Plan (Last Cut)").Select
    Range("AG745").Select '(Always cell AG745)
    Selection.Copy
    Sheets("Headcount").Select
    Range("J6").Select '(Initially paste to cell J6)
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J7").Select '(Subsequently paste to cell J7, J8, J9 etc.)
    
End Sub

Thanks

Vince

[mdmackillop]

Hi Vince
Can you post a workbook showing expeted result after say 3 weeks.

[offthelip]

Try this:

[VBA]Sub loopsub()
Dim inarr As Variant

Worksheets("Headcount").Select
For i = 1 To 53
Worksheets("Dashboard").Range("R8").Value = i
inarr = Worksheets("The Plan (Last Cut)").Range("AG745")
Worksheets("Headcount").Range(Cells(i + 5, 10), Cells(i + 5, 10)) = inarr
Next i

End Sub
[/VBA]

[skyline501]

Hi Vince
Can you post a workbook showing expeted result after say 3 weeks.

Hi,

I've attached the 3 relevant sheets as the model exceeds the file size limit.

Dashboard! Cell R8 is the week number needing to count from 1 - 53
The Plan (Last Cut)! Cell AG745 is the result to be copied to
Headcount! Cell J6 for week 1, Cell J7 for week 2 etc. Rinse and repeat.

Thanks.

Vince

[mdmackillop]

offthelip's code seems to do what you are after.

[skyline501]

Hi,

I tried it earlier today and it hung, I didn't notice the typo.
Thank you for the correction it now works fine.

Top site,

Many thanks
Vince

[skyline501]

Many thanks offthelip this works a treat.

Regards

Vince