Code to copy and paste one range to another, multiple times in VBA excel

Question

I have written the following code to copy and paste range w21:W1759 into range AD21:

Sub CommandButton1_Click()

    Dim i As Integer, j As Integer
    For j = 1 To Range("d7")
     Range("d8") = j

     'Calculate
        Range("w21:W1759").Select
        Selection.Copy
             Range("AD21").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
      Next j
End Sub

My data in range w21:W1759 is set to change (due to random sampling) on every click and I want the new data in this range to be copied and pasted to range "ae" (the adjacent column). Then on the next click to "af" and so on and so on. What code do I need to add to the above to achieve this?

Thanks very much for the help

score 1 · Accepted Answer · 2016-02-22 06:49:24Z

1

This will depend somewhat on what is to the right of column AC. If column Ad is the first empty column then it is easy to copy to. Subsequent copying operations can use the same next-empty-column method to fill columns AE, AF, etc.

Sub CommandButton1_Click()
    Dim i As Long, j As Long

    With Worksheets("Sheet1")
        For j = 1 To .Range("d7")
            .Range("d8") = j
            .Calculate
            With .Range("w21:w1759")
                .Parent.Cells(21, Columns.Count).End(xlToLeft).Offset(0, 1).Resize(.Rows.Count, .Columns.Count) = .Value
            End With
        Next j
    End With

End Sub

I've altered your Copy, PasteSpecial Values method to be a direct value transfer. This is faster and does not involve the clipboard.

edited Feb 22, 2016 at 6:49

answered Feb 21, 2016 at 14:17

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

Hi Jeeped. Thank you very much for the revised code. However, when I type (e.g. 2, or 3) in range d7 the values from w21:w1759 are still moved to column AD (overwrite each time), instead of moving to AE, AF etc. Any ideas?

user4039065 Over a year ago

I don't know how that could be occurring. I did miss a call to the .Parent object which I have corrected above but that would not have any impact on what you are describing.

Matt Over a year ago

Hi Jeeped I have used your new code, but no joy. When I click on my control button to run 3,4,5 or 50 (d7) random re-samples to get new results in W21:w1759 the results from this range always copy and paste to the same column AD instead of moving one column over each time? Any more thoughts???

user4039065 Over a year ago

Take another look at the code. Do you have a value in W21? If not then there is nothing to put int AD21 and nothing to find when looking from the right to relocate.

Matt Over a year ago

Hi Jeeped. I have double checked and there is definitely a value in w21 and it copies fine to AD21. The problem is that each time the values in w21:w1759 copy they keep copying to column ad everytime. Perhaps I can send you my spreadsheet as I'm probably not explaining myself properly?

|

Collectives™ on Stack Overflow

Code to copy and paste one range to another, multiple times in VBA excel

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related