Copy paste loop a number of times as the cell value - VBA

Question

I am trying to build a loop to copy and paste a range of cells from one excel worksheet to another a number of times, as specified in a cell A1.

    Sub AnalogPointBuild()

                Dim i As Integer
                Dim y As Integer

                'only copy the number of times as the count in cell A1 of worksheet "inputs"
                Sheets("inputs").Activate
                y = Range("A1").Value

                ' copy the range of cells from one worksheet "Analog Template' to the "Analog Output"        

                For i = 1 To y
                Worksheets("Analog Template").Range("B3:EU3").Copy Worksheets("Analog Output").Range("B3:EU3")
                Next i

    End Sub

I understand that during the iteration, the paste does not increment the cell value and paste it in the next one down.

Help! What's wrong with my code?

You are not changing your paste destination. Plus you don't need to loop (or copy and paste) - use Resize. — SJR
– SJR, Commented Jan 5, 2018 at 17:39
Watch what happens if you use F8 to step through your code as the for loop executes.. — Chris Meurer
– Chris Meurer, Commented Jan 5, 2018 at 17:43

SJR · Accepted Answer · 2018-01-05 17:45:41Z

2

Try this, as per comments above.

Sub AnalogPointBuild()

Dim y As Long

'only copy the number of times as the count in cell A1 of worksheet "inputs"
y = Sheets("inputs").Range("A1").Value

' copy the range of cells from one worksheet "Analog Template' to the "Analog Output"
Worksheets("Analog Output").Range("B3:EU3").Resize(y).Value = Worksheets("Analog Template").Range("B3:EU3").Value

End Sub

answered Jan 5, 2018 at 17:45

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

2 Comments

ksenia Over a year ago

I did not know about the resize property! Much appreciated, thank you, @SJR!

SJR Over a year ago

Very useful it is. Use it whenever you can!

Subodh Tiwari sktneer · Accepted Answer · 2018-01-05 17:50:49Z

1

You don't need a for loop, rather you can try it like this...

Sub AnalogPointBuild()
    Dim y As Integer

    'only copy the number of times as the count in cell A1 of worksheet "inputs"
    y = Sheets("inputs").Range("A1").Value

    ' copy the range of cells from one worksheet "Analog Template' to the "Analog Output"

    Worksheets("Analog Template").Range("B3:EU3").Copy Worksheets("Analog Output").Range("B3:EU3").Resize(y)

End Sub

answered Jan 5, 2018 at 17:50

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Collectives™ on Stack Overflow

Copy paste loop a number of times as the cell value - VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related