Hello, I have this code behind my worksheet that is really slow and sluggish. I know there must be an easier way because the concept behind the code itself is not all too complex. The purpose of this code is to loop through column BO and paste each value 7 times. After a value is pasted, the code should move down to the next row. Each value should be pasted in column BW in the next empty row. Any help would be greatly appreciated!
Code:
Sub CopyNames ()
Dim x As Long, numberOfTimes As Long 'number of times to "copy"
Dim rngToCopy As Range
Dim rngToPaste As Range
Set pasteSheet = Worksheets("Report")
'## Define the range you want to copy:
Set rngToCopy = Range("BO10")
'## Get the number "X" from cell D1:
numberOfTimes = Range("BO7").Value
w = 1
For Each r In Range("BO9", Range("BO" & Rows.Count).End(xlUp))
rngToCopy.copy
For x = 1 To numberOfTimes
'# Copy and Paste
pasteSheet.Cells(Rows.Count, "BW").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Next
rngToCopy = rngToCopy.Offset(w, 0)
w = w + 1
Next
End Sub
