VBA - how to repeat a set number of values X times
NELM -
Jun 10, 2018 at 03:18 AM - Latest reply: vcoolio1195
Posts Thursday July 24, 2014Registration dateModeratorStatus
September 22, 2018
Last seen
- Jun 10, 2018 at 04:53 AM
Try the following code, placed in a standard module and assigned to a button:-
Sub CopySelection()
Dim lr As Long, i As Integer
Dim SCT As String
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
SCT = InputBox("Please enter the number of times to copy data.")
If SCT = vbNullString Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To SCT
Sheet1.Range("A2:A" & lr).Copy Sheet1.Range("C" & Rows.Count).End(3)(2)
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code assumes that the values to copy are in Column A starting in row2 with a heading in row1. The values are copied to Column C (x times) based on the number placed in the Input Box that appears on clicking on the button.
Following is the link to a little sample that I have prepared showing how the code works:-
Click on the "RUN" button . When the Input Box appears, enter the number of times (1, 2, 3 etc....) you wish to copy the Column A data and then click on OK.
