Counter in For Each loop

[Possum17]

I created a file which will calculate sales rep commission based on terms and conditions of the commission agreement. All sales rep performance data is filled in on one tab, and then through selections on drop down boxes, the file will generate the correct payout to be paid to relevant sales reps.

The file functions correctly for manually doing one rep at a time. This is very time consuming and seems like something that could be automated in vba. I post the macro I have written below. I *think* this functions correctly in all aspects except it always pastes values to outputs in AH/AI/AJ/AK6. Each line is unique to a sales rep, so I think I need a counter in my For Each loop which will bump the paste target cells down one line after each AH/AI/AJ/AK set. I am having trouble getting this part of the code to work. I tried to insert a For To counter within the code in various spots but I couldn't get it to work.

Any input appreciated!

ps. if it wasn't obvious enough already, I am very new to writing my own VBA

Sub ForEachRepEnhanced()
'
' ForEachRep Macro
' Loops each rep
'

'
    Dim Cell As Range
    
    For Each Cell In Sheets("1.Information from Target Sheet").Range("B6:B31")
            Sheets("Rep Select").Range("D3").Value = Cell.Value
            Sheets("3. Payout").Select
            Range("D9:H11").Select
            Selection.ClearContents
            Sheets("2. Sales data").Select
            Range("D3").Value = 1
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Range("AH6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D9").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 2
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Range("AI6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D10").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 3
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Range("AJ6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D11").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 4
            Application.CutCopyMode = False
            Range("D4").Select
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Range("AK6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D9:H11").Select
            Application.CutCopyMode = False
            Selection.ClearContents
    Next Cell
End Sub

 

[Possum17]

Stepping through, I also think this part is not working. I want this to update a cell based on the cell reference in the loop, but that doesn't seem to work.

Sheets("Rep Select").Range("D3").Value = Cell.Value

 

[Possum17]

Think I figured it out. This is what I ended up with. Seems to work.

Sub ForEachRepEnhanced()
'
' ForEachRep Macro
' Loops each rep
'

'
    
    For Each Rng In Sheets("Rep data").Range("B6:B31")
            Range("A1").Value = Rng.Value
            Sheets("3. Payout").Select
            Range("D9:H11").Select
            Selection.ClearContents
            Sheets("2. Sales data").Select
            Range("D3").Value = 1
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Rng.Offset(0, 32).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D9").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 2
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Rng.Offset(0, 33).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D10").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 3
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Rng.Offset(0, 34).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D17:H17").Select
            Application.CutCopyMode = False
            Selection.Copy
            Range("D11").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("2. Sales data").Select
            Range("D3").Value = 4
            Application.CutCopyMode = False
            Range("D4").Select
            Sheets("3. Payout").Select
            Range("D21").Select
            Selection.Copy
            Sheets("Rep data").Select
            Rng.Offset(0, 35).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("3. Payout").Select
            Range("D9:H11").Select
            Application.CutCopyMode = False
            Selection.ClearContents
            Sheets("Rep Data").Select
            Range("A1").Select
            Selection.ClearContents
            ClearClipboard
    Next Rng
End Sub