Thread: activecell.copy and offset

Here you go.

Sub test()
Dim TestCell As String
TestCell = "$A$10"

    'check if active cell is A10
    If ActiveCell.Address = TestCell Then
        
        'copy
        ActiveCell.Copy
        
        'Resume next just in-case 9 cells above TestCell don't exist
        On Error Resume Next
        
        'paste loop to go back 9 cells
        For i = 1 To 9
        ActiveCell.Offset("-" & i, 0).PasteSpecial
        
        'go back to TestCell
        Range(TestCell).Activate
        Next i
    End If
End Sub

See attached for example too.

Offset works like so:-

Offset

The Offset property is the one that you will use the most with Range to move around the sheet.

To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (from B2 to C2): Range("B2").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select

Reference:- http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm

So list out your 'TestCells' in a seperate sheet (or within your database). lookup each testcell and run my code? (you'd have to change the range I:A as per above)

ActiveCell.Copy
ActiveCell.Offset(0,-10).Select

Have a look at http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm for offset