Excel VBA Copy & Paste a Range of Cells Repeatedly in a Column

Question

I tried this in two different ways.

First, select the range of cells to copy, and select the range of destination to paste. Below is the code:

Sub PanelData()

Dim size As Integer
Dim i As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet


Set shrate = Sheets("Rate")
Set shpanel = Sheets("Panel")

size = shrate.Range("B4").End(xlDown).Row

shrate.Range(Cells(4, 2), Cells(size, 2)).Select
Selection.Copy

shpanel.Cells(1, 1).Value = size - 3

For i = 1 To 18

shpanel.Range(Cells(4, 1).Offset((i - 1) * (size - 3), 0), Cells(3, 1).Offset(i * (size - 3), 0)).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Selection.NumberFormat = "m/d/yyyy"

 Next i

End Sub

Second, copy cells in the range, and paste to destination column one by one. Here is the code:

Sub LoopingCP()
Dim size As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet

Set shrate = Sheets(2)
Set shpanel = Sheets(4)

size = shrate.Cells(4, 2).End(xlDown).Row - 3

For x = 1 To 18

    For i = 1 To size
        shrate.Cells(i + 3, 2).Select
            Selection.Copy

        shpanel.Cells(x * (i + 3), 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Selection.NumberFormat = "m/d/yyyy"

    Next i

Next x
End Sub

Neither of these attempts worked out. What did I do wrong?

Thanks

How didn't they work out? Did you get an error via VBA? If so, what error at what line? Does it run, but not copy/paste as expected? Finally, please see [how to avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), as it can cause funky things to happen if you're not careful. — BruceWayne, Nov 17 '16 at 22:50

score 0 · Accepted Answer · answered Nov 17 '16 at 23:33

A couple things prevented that from working. First, when using a Range variable, such as Range(),Cells(),Rows(),Columns(), etc. you should always be explicit with the sheet you expect to run that on.

Secondly, you want to avoid using .Select, as I linked to in the comment. Effectively, you just "back up" the two rows ending with, and starting with, .Select/.Selection

See if this works:

Sub PanelData()

Dim size As Integer
Dim i As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet


Set shrate = Sheets("Rate")
Set shpanel = Sheets("Panel")

size = shrate.Range("B4").End(xlDown).Row

shrate.Range(shrate.Cells(4, 2), shrate.Cells(size, 2)).Copy

shpanel.Cells(1, 1).Value = size - 3

For i = 1 To 18
    With shpanel.Range(shpanel.Cells(4, 1).Offset((i - 1) * (size - 3), 0), shpanel.Cells(3, 1).Offset(i * (size - 3), 0))
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "m/d/yyyy"
    End With
 Next i

End Sub

Sub LoopingCP()
Dim size As Integer
Dim shrate As Worksheet
Dim shpanel As Worksheet

Set shrate = Sheets(2)
Set shpanel = Sheets(4)

size = shrate.Cells(4, 2).End(xlDown).Row - 3

For x = 1 To 18
    For i = 1 To size
        shrate.Cells(i + 3, 2).Copy
        With shpanel.Cells(x * (i + 3), 1)
            .PasteSpecial Paste:=xlPasteValues
            .NumberFormat = "m/d/yyyy"
        End With
    Next i
Next x
End Sub

FYI - when you just want the values, you can set the ranges' values equal to eachother, instead of using .Copy/.Paste, which saves the clipboard from being used, and saves a little time when running the macro:

Range([destination range]).Value = Range([copy from range]).Value (and please note you need to include the sheet name before Range(), of course.

Thank you so much for your answer! It works like a charm! – MDL Nov 22 '16 at 18:05 — MDL, Nov 22 '16 at 18:05

Excel VBA Copy & Paste a Range of Cells Repeatedly in a Column

1 Answers1