Help using a macro to iterate through a range of rows

[kevinmd70]

Hi, I am trying to loop through the following macro to work through a range of rows.


My goal is to end up with a sheet (Allocation per Employee) populated with the employee department and name and an allocation of time across projects. The Scratch sheet contains a number of formulas that calculate an allocation based on the pasted values from ADP Output.


I am new to macros and not sure how to create a loop to increment the rows/ranges and work through all of the employees. Guidance would be greatly appreciated!


Sub Macro1()
'
' Macro1 Macro
'




'Copy employee department and name from ADP Output and paste to the Allocation Per Employee
Sheets("ADP Output").Select
Range("A5:B5").Select
Selection.Copy
Sheets("Allocation per Employee").Select
Range("A2").Select
ActiveSheet.Paste
'Select time entry data for the employee and copy to the Scratch sheet. The Scratch sheet contains an allocation model to split time across projects.
Sheets("ADP Output").Select
Range("C5:AC5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Scratch").Select
Range("C2").Select
ActiveSheet.Paste
'Capture the allocation output on the Scratch sheet and paste it to the Allocation per Employee sheet
Range("C60:J60").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Allocation per Employee").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data. In the meantime, your current code can be simplified to this:

Sub Macro1()
    'Copy employee department and name from ADP Output and paste to the Allocation Per Employee
    Sheets("ADP Output").Range("A5:B5").Copy
    Sheets("Allocation per Employee").Range ("A2")
    'Select time entry data for the employee and copy to the Scratch sheet. The Scratch sheet contains an allocation model to split time across projects.
    Sheets("ADP Output").Range("C5:AC5").Copy Sheets("Scratch").Range("C2")
    'Capture the allocation output on the Scratch sheet and paste it to the Allocation per Employee sheet
    Range("C60:J60").Copy
    Sheets("Allocation per Employee").Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[kevinmd70]

Thanks! I will see if I can scrub the file and share it.