VBA Code Needs Revamping

[Topher177]

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!

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

 

[oldbrewer]

22				123	first 10 rows are previously entered data	45	this cell equalled 10 before the macro ran
33				234
44				345
55				456
66				567
				678
				789
				987
				876
				765
				22					macro
				22
				22					rownum = Cells(1, 10)
				22					For j = 1 To 6
				22					For k = 1 To 7
				22					rownum = rownum + 1
				22					Cells(rownum, 5) = Cells(j, 1)
				33					Next k
				33					Next j
				33					End Sub
				33
				33
				33
				33
				44
				44
				44
				44
				44
				44
				44
				55
				55
				55
				55
				55
				55
				55
				66
				66
				66
				66
				66
				66
				66

<colgroup><col span="14"></colgroup><tbody>
</tbody>