Paste 13 values 2500 times using a Loop

Question

I am new to VBA scripting - and have been trying to - copy values from sheet 1 "C1:P1" and pasting them in another sheet, say Sheet 2, in the column H (beginning from H2 to H2500). Basically the code needs to copy the values, transpose them and paste them.

Sub Run()
'
' Run Macro
'

    Dim i As Long

    For i = 1 To 2500
        Sheets("Sheet1").Range("C1:P1").Select
        Selection.Copy
        Sheets("Sheet2").Range("H2:H2500" & i).Offset(13 * i, 0).Select
        Selection.PasteSpecial Paste:=x1PasteFormulas, Operation:=x1None, Skipblanks:= _
            False, Transpose:=True

    Next i

End Sub

The values for Sheet 1 from C1 to P1 have to be copied and pasted into the column H in Sheet 2, each Value gets pasted 13 times, AND there are a total of 165 values to be copied over into sheet 2(column H). The Run time error '1004' is 'Select method of range class failed', any help would be appreciated!! :)

What you want to do is kind of hard to follow. You want the values in C1:P1 to be in H1:U2500, is that right? — sous2817, Apr 11 '18 at 00:45
That error has more than a number. It has a text description that is right on the screen in front of you, which means there is zero reason for you to fail to put it in your question so we have it as well. You're asking us for free help to solve your problem, and it's in your best interest to make it as easy as possible to do so by providing the details that are *right on your screen*. — Ken White, Apr 11 '18 at 00:47
The values for Sheet 1, C1 to P1 have to be copied and pasted into the column H in Sheet 2, each Value gets pasted 13 times, AND there are a total of 165 values to be copied over into sheet 2(column H). The Run time error is Select method of range class failed. Sorry, for not mentioning it earlier. — Devyani Chaturvedi, Apr 11 '18 at 01:08
`Select - Copy - Paste` is almost never needed. If all you want to do is to transfer values -- you can use the `.Value` property of the corresponding ranges. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10717999/4996248). — John Coleman, Apr 11 '18 at 01:13
There's an [edit] link right below the tags. Please use it to add details to the question itself, rather than burying them in comment clutter. Thanks. — Ken White, Apr 11 '18 at 01:17

paul bica · Accepted Answer · 2018-04-11T08:26:13.043

1

You are copying too many times: ...Range("H2:H2500" & i).Offset(13 * i, 0)...

And as mentioned, remove the .Select Selection statements

Option Explicit

Public Sub CopyCPRow()
    Dim ws1 As Worksheet:   Set ws1 = Sheet1
    Dim ws2 As Worksheet:   Set ws2 = Sheet2
    Dim r As Long, hdr As Range, lr As Long: lr = 2500

    Set hdr = ws1.Range("C1:P1"): hdr.Copy
    With ws2
        Application.ScreenUpdating = False
        For r = 2 To lr Step hdr.Columns.Count
            .Range("H" & r).PasteSpecial Paste:=xlPasteFormulas, _
                                         Skipblanks:=False, Transpose:=True
        Next
        .Rows(lr + 1 & ":" & r).Delete:     .Activate
        Application.ScreenUpdating = True:  .Cells(lr + 1, "H").Select
    End With
End Sub

edited Apr 11 '18 at 08:26

answered Apr 11 '18 at 01:24

paul bica

10,557
4
23
42

OMG that works!! thanks heaps! I've been trying to fix this for last one week!! – Devyani Chaturvedi Apr 11 '18 at 01:27
Hi Paul, I had a question, if I were to copy B2:B165 from sheet one and paste it in sheet2 in column D starting at D2 - something like B2:B4 are 1.) xxx, 2.) yyy, 3.) zzz and I wanted them pasted as - xxx 14 times down the column in Sheet2 (D column) followed by yyy 14 times followed by zzz.. where will the code change? I tried a few tweaks and nothing seemed to work. – Devyani Chaturvedi Apr 12 '18 at 00:43
So you want to copy `Sheet1.B2 to Sheet2.D2:D14`, then `Sheet1.B3 to Sheet2.D15:D28`, then `Sheet1.B4 to Sheet2.D29:D42`, and so on? – paul bica Apr 12 '18 at 00:52
Well, you need different logic (can't just modify the code above), but open a new question (and provide 2 screen captures showing the first 3 values on Sheet1, and how they will look at the end on Sheet2, and your short attempt at modifying the code) – paul bica Apr 12 '18 at 00:57
Well I can't ask another question because someone here flagged me or something..not sure how it works. But thanks anyway for your help. :) – Devyani Chaturvedi Apr 12 '18 at 01:03
The system doesn't allow you to ask another question? – paul bica Apr 12 '18 at 01:06
Yeah, apparently my question was not well received by the community or something.. so I can't ask another question for another couple of days – Devyani Chaturvedi Apr 12 '18 at 01:07
Done! Thanks heaps!! – Devyani Chaturvedi Apr 12 '18 at 01:16

DisplayName · Answer 2 · 2018-04-11T06:01:55.323

you don't need loop:

Public Sub CopyCPRow()
    Worksheets("Sheet1").Range("C1:P1").Copy

    Worksheets("Sheet2").Range("H2:H35001").PasteSpecial Paste:=xlPasteFormulas, _
                                                                  Skipblanks:=False, _
                                                                  Transpose:=True
End Sub

or, if you don't want to leave the math to Excel:

Public Sub CopyCPRow()
    Dim nCopy As Long

    nCopy = 2500
    With Worksheets("Sheet1").Range("C1:P1")
        .Copy
        Worksheets("Sheet2").Range("H2").Resize(.Columns.Count * nCopy).PasteSpecial Paste:=xlPasteFormulas, _
                                                                                      Skipblanks:=False, _
                                                                                      Transpose:=True
    End With
End Sub

DeanOC · Answer 3 · 2018-04-11T01:40:18.660

Sheets("Sheet1").Range("C1:P1").Select won't work unless Sheet1 is currently active, and likewise Sheets("Sheet2").Range("H2:H2500" & i).Offset(13 * i, 0).Select will fail unless Sheet2 is active.

However you don't need to activate each sheet to copy/paste, and you can simplify your code to

Sub Run()
'
' Run Macro
'

    Dim i As Long

    For i = 1 To 2500
        Sheets("Sheet1").Range("C1:P1").Copy
        Sheets("Sheet2").Range("H2:H2500" & i).Offset(13 * i, 0).PasteSpecial Paste:=x1PasteFormulas, Operation:=x1None, Skipblanks:= _
        False, Transpose:=True

    Next i

End Sub

BTW, C1:P1 is actually 14 cells wide, so your offset should be 14 not 13, as you will be overwriting the last cell each time.

Also, Sheets("Sheet2").Range("H2:H2500" & i) appears to have a typo, as the & i will cause the target Range to get bigger each time, and as you are pasting a static range, there is no need for this. You could get away with just using Sheets("Sheet2").Range("H2:H14")

Hi Dean, I just tried this and got the Run-time error message : '1004' - PasteSpecial method of Range class Failed. — Devyani Chaturvedi, Apr 11 '18 at 01:22
I think that your problem is because your are specifying `Sheets("Sheet2").Range("H2:H2500" & i)`. The ` & i` is creating a huge range which exceeds the size of the sheet. As you are only pasting 13 values, you can just make the target range `Sheets("Sheet2").Range("H2:H14")` — DeanOC, Apr 11 '18 at 01:30

Paste 13 values 2500 times using a Loop

3 Answers3