For Loop Copy and Paste Excel VBA

Question

Function Iterate()
Dim i As Integer
For i = 1 To 10
Worksheets("Calculator").Calculate
Worksheets("Calculator").Range("AC6:AC16").Copy  Destination:=Sheets("Iterations").Range("A1:A10")
Worksheets("Calculator").Range("AT10:AT11").Copy Destination:=Sheets("Iterationas").Range("A11:A12")
                         Worksheets("Iterations").Paste

Next

End Function

My Goal, is to run this loop as many times as I'll need, and after every single loop, I want excel to take the Range("cells") and copy them to the "Iterations" worksheet.

The data on Calculator is refreshed every loop, so new calculations appear. Once the new calculations appear, I want to paste it one next to the other (which I don't know how to do).

For now, this gives me a runtime error Subscript it out of range

Any advice?

are AC6, AC16 variables? if so, how are they defined? if not, they need to be in quotes... and use a colon instead of a comma... where are you learning this code? it makes no sense — braX
– braX, Commented Jul 12, 2017 at 12:15
I'm trying to pick it up as fast as I can.. I'm more familiar with Python Syntax and every loop, AC6,AC16 are changing because of randbetween() function — Yoav Poni
– Yoav Poni, Commented Jul 12, 2017 at 12:15
a range definition needs to look more like this Worksheets("Calculator").Range("AC6:AC16") — braX
– braX, Commented Jul 12, 2017 at 12:21
Yeah, I changed it, and now I'm at the Runtime Error, Subscript out of range. would you know how I would be able to paste the data at a column one after the other? — Yoav Poni
– Yoav Poni, Commented Jul 12, 2017 at 12:22
changed it to what? are you expecting people to read your mind? update your post as you go so people can see what you did. — braX
– braX, Commented Jul 12, 2017 at 12:26

Vityata · Accepted Answer · 2017-07-12 13:13:06Z

0

I have tried the following:

Option Explicit

Public Sub Iterate()

    Dim i As Long

    For i = 1 To 10
        Worksheets(1).Calculate
        Worksheets(1).Range("AC6:AC16").Copy Destination:=Worksheets(2).Range("A1:A10")
        Worksheets(1).Range("AT10:AT11").Copy Destination:=Worksheets(2).Range("A11:A12")
    Next

End Sub

It works, just make sure that you rename the Worksheets(1) and (2) relevantly. In general, use Function, when you expect a value to be returned. For changes in a worksheet, use Sub.

In general, what you wanted in the comments is to copy the values and to put them next to each other column. Here is how to get it:

Option Explicit

Public Sub Iterate()

    Dim i As Long

    For i = 1 To 10
        With Worksheets(1)

        Worksheets(2).Calculate

        Worksheets(2).Range("AC6:AC16").Copy
        .Cells(1, i).PasteSpecial Paste:=xlPasteValues

        Worksheets(2).Range("AT10:AT11").Copy
        .Cells(1, i + 10).PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False
        End With
    Next

End Sub

edited Jul 12, 2017 at 13:13

answered Jul 12, 2017 at 12:31

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

6 Comments

Yoav Poni Over a year ago

Thank you.. In order for me to paste special should I just write PasteSpecial:= (after each worksheet)?? and then the social paste I want?

Yoav Poni Over a year ago

And the reason I have this loop, comes from the fact that every refresh, the excel page generate new numbers that make a different calculations. That is why I wanted to Paste the SpecialValues, and after I run this 10 times the calculations will show up in the other sheet one column next to the other.

Vityata Over a year ago

@YoavPoni - I think I got what you want. See the edited answer.

Yoav Poni Over a year ago

Thank you so much! @Vityata

Yoav Poni Over a year ago

is there a reason for the Excel file to go wrong when the I execute the Macro?

|

Collectives™ on Stack Overflow

For Loop Copy and Paste Excel VBA

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