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

JDSMITH

New Member
Joined
Sep 30, 2013
Messages
1
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?​
 
Hi

This is working for me:

Code:
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top