Macro that copies data from one sheet and pastes it to another x columns to the right

[JDSMITH]

Ideally, what I need is a macro that copies the data from cells A2:A4, B2:B4, C2:C4 and paste it into a different sheet a specified number of columns to the right, dependent upon the vale that is in the corresponding cell A1, B1 or C1. So for example, if the data were:

A1 = 11, B1 = 7, C1 = 9
A2 = 50, B2 = 200, C2 = 500
A3 = 150, B3 = 400, C3= 100

Then the macro would paste the values in cells A2:A3 in cells L2:L3 in a different sheet (11 columns to the right of column A). Similarly it would paste the value in cells B2:B3 into cells I2:I3 (7 columns to the right of column b) and would paste the values of cells C2:C3 into cells L2:L3 (9 columns to the right of column c). Since the values of the cells in A2:A3 and C2:C3 are both pasted into cells L2:L3 it would add the values together so L2 = (50 +500) = 550, L3 = (150 + 100) = 250.

Does this make sense? Is this possible? Is this the offset function?​

 

[Chris Mack]

Hi

This is working for me:

Sub Move()
    Dim cell As Range, rng As Range
    Set rng = Range("A2:C3")
    For Each cell In rng
        If Worksheets("Sheet2").Cells(cell.Row, cell.Column + Cells(1, cell.Column).Value) <> "" Then
        Worksheets("Sheet2").Cells(cell.Row, cell.Column + Cells(1, cell.Column).Value) = cell.Value + Worksheets("Sheet2").Cells(cell.Row, cell.Column + Cells(1, cell.Column).Value)
        ElseIf Worksheets("Sheet2").Cells(cell.Row, cell.Column + Cells(1, cell.Column).Value) = "" Then
        Worksheets("Sheet2").Cells(cell.Row, cell.Column + Cells(1, cell.Column).Value) = cell.Value
        End If
    Next cell
End Sub

Run this code while the sheet containing your original values is active.

Test in a practice workbook first.

Hope this helps,

Chris.