PDA

View Full Version : [SOLVED] Need help creating a loop please



skyline501
03-30-2017, 11:27 AM
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
03-30-2017, 01:25 PM
Hi Vince
Can you post a workbook showing expeted result after say 3 weeks.

offthelip
03-30-2017, 03:43 PM
Try this:

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

skyline501
03-31-2017, 02:07 AM
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
03-31-2017, 03:20 AM
offthelip's code (http://www.vbaexpress.com/forum/showthread.php?59047-Need-help-creating-a-loop-please&p=359105&viewfull=1#post359105) seems to do what you are after.

skyline501
03-31-2017, 04:47 AM
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
03-31-2017, 04:51 AM
Many thanks offthelip this works a treat.

Regards

Vince