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