CODEWITHSUNDEEP
vbaexcelmacros
Ongok Ongoksepen
Ongok Ongoksepen

Reputation: 305

Copy and paste VBA excel

Sub Test()

Dim x As Integer

For x = 1 To 1000

Sheets("Test1").Range(Cells(x, 1), Cells(x, 10)).Select
Sheets("Test2").Cells(5, 16).PasteSpecial Paste:=xlValues, Transpose:=True

Next

End Sub

Here is my macro, Purpose of this macro, copy Range "Ax:Jx" from Sheet Test 1 to Range "P5:P14" in sheet Test 2

Note : I want to run 1000 scenario to see the impact on some results. So first step, I create looping macro for scenario as you can see above.

I'm stuck at "Sheets("Test1").Range(Cells(i, 1) & Cells(i, 10)).Select" . How to define this range so that it can loop as x looping.

Upvotes: 0

Views: 437

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

No need for select or copy/paste:

Sub Test()

    Dim x As Integer, sht1, sht2

    Set sht1 = Sheets("Test1")
    Set sht2 = Sheets("Test2")

    For x = 1 To 1000

        sht2.Cells(5, 16).Resize(10, 1).Value = _
              Application.Transpose(sht1.Cells(x, 1).Resize(1, 10).Value)

    Next

End Sub

Upvotes: 3

Related Questions