Macro for copying from one variable range to another variable range

Question

I have a column of data of which I would like to copy a range of four cells to a another range in a different worksheet. This should be in a loop which changes the starting cells to copy every time.

I am more or less new to macro programming and can't get beyond the following code, which already at the beginning gives me a compile error ("Sub or function not defined), I am not sure what the reason for that is... Also, I have a feeling that I am changing my variables, but not actually printing something into the cells.

Any help would be greatly appreciated!

Cheers, dahlai

Sub CopyingPeriod2()
    Dim ws As Worksheet
    Set ws = Worksheets("1")

    Dim OneCells As Range
    Set OneCells = ws.Range("C1:C4")

    Dim works As Worksheet
    Set works = Worksheet("New")

    Dim NewCells As Range
    Set NewCells = works.Range("J51:J54")

    Do Until ActiveCell.Value = ""

        NewCells.Value = OneCells.Value

        OneCells = OneCells.Offset(8, 0)
        NewCells = NewCells.Offset(0, 1)


    Loop

End Sub

Update:

Answering the comments: The C1:C4 range is to be offset by 8 rows every time the loop runs. The J51 cell is to be offset by 1 column every time the loop runs.

I continued trying and came up with the following code (No loop included,yet): Copy and paste works. However, when the OneCells is updated the cells of the original range in the worksheet go blank and OneCells.Select selects the same range as before

Sub CopyingPeriod2d()

    Dim OneCells As Range
    Set OneCells = Range("C1:C4")

    Worksheets("1").Activate
    OneCells.Select
    Selection.Copy
    Worksheets("New").Activate
    Range("J51").PasteSpecial xlPasteValues

    Worksheets("1").Activate
    OneCells.Select
    ActiveCell.Offset(8, 0).Select
    OneCells = Range(ActiveCell, ActiveCell.Offset(4, 0))
    OneCells.Select

Could you tell us what the ranges that need to be copied/pasted are? And how they will vary? — Kyle
The undefined sub or function is in the line "Worksheet("New")" It needs to be Worksheets("New") unless you are intending to create a new worksheet, in which case you need something different. This, of course, assumes that you havea worksheet titled "New" — OpiesDad
Someone correct me if I'm wrong, but I don't believe your active cell will change at all through your loop because the active cell doesn't change unless you tell it to. So you will probably end up in an infinite loop or your loop wont fire depending on the active cell when you start the macro. — D_Zab
Hey guys, thanks for the feedback. Thorough update in the edited post in a minute. — Dahlai

Dahlai Dahlai · Accepted Answer · 2015-03-03T16:07:56

Ultimately, got it to work =)

Sub CopyingPeriod2d()

    Dim worksh As Worksheet
    Dim ws As Worksheet
    Dim OneCells As Range
    Dim NewCells As Range

    Set worksh = Sheets("1")
    Set ws = Sheets("New")


'AcCoa data copy
    Set OneCells = worksh.Range("C1:C4")
    Set NewCells = ws.Range("J51")

    i = 1


    'Selection of first cells, else the loop would not run properly since it is checking for the ActiveCell content
    Sheets("1").Activate
    OneCells.Select


    Do Until ActiveCell.Value = ""
        OneCells.Copy

        'Paste to destination and update destination for next iteration of the loop
        NewCells.PasteSpecial xlPasteValues
            If i = 6 Or i = 12 Then
                Set NewCells = NewCells.Offset(0, 3)
            Else
                Set NewCells = NewCells.Offset(0, 1)

            End If
            i = i + 1


        Set OneCells = OneCells.Offset(12, 0)
        OneCells.Select

    Loop

Macro for copying from one variable range to another variable range

1 Answers