Try this code, I like to stay away from copy and pasting:
Option Explicit
Dim ws As Worksheet
Sub RepeatDataWithColorAndClearColumns()
Dim repeatCount As Integer
Dim i As Integer
' Set reference to Sheet1
Set ws = Worksheets("Hole")
' Prompt user for repeat count
repeatCount = InputBox("Enter the number of times to repeat the data:", "Repeat Data")
' Validate input
If Not IsNumeric(repeatCount) Then
MsgBox "Invalid input. Please enter a numeric value.", vbExclamation
Exit Sub
End If
' Convert input to integer
repeatCount = CInt(repeatCount)
' Set starting row
Dim startRow As Long
startRow = 21
' Copy data from rows 3 through 20 and paste it repetitively
Dim holeOneRowColor As Long
Application.ScreenUpdating = False
For i = 1 To repeatCount
' copy the rows
CopyEighteenHoles startRow, i
' Shade the first row in the paste range from column 1 through 13 with a light color
holeOneRowColor = GetColor(i)
ws.Range("A" & CStr(startRow) & ":M" & CStr(startRow)).Interior.Color = holeOneRowColor
' Increment startRow by number of copied rows
startRow = startRow + 18
Next i
Set ws = Nothing
Application.ScreenUpdating = True
End Sub
Private Sub CopyEighteenHoles(startRow As Long, repeatCount As Integer)
Dim eighteenHolesRow As Long
Dim columnToCopy As Integer
Dim newDataStartingRow As Long
newDataStartingRow = startRow
' copy the information from the first 18 rows to the next 18
For eighteenHolesRow = 3 To 20
For columnToCopy = 1 To 10
ws.Cells(newDataStartingRow, columnToCopy).Value = ws.Cells(eighteenHolesRow, columnToCopy).Value
Next columnToCopy
' increment the number in col K
ws.Cells(newDataStartingRow, "K").Value = ws.Cells(eighteenHolesRow, "K").Value + repeatCount
newDataStartingRow = newDataStartingRow + 1
Next eighteenHolesRow
End Sub
Private Function GetColor(ByVal index As Integer) As Long
' Function to get a light color based on the index
Select Case index Mod 10
Case 0
GetColor = RGB(196, 215, 155) ' Light green
Case 1
GetColor = RGB(222, 193, 218) ' Light purple
Case 2
GetColor = RGB(184, 204, 228) ' Light blue
Case 3
GetColor = RGB(248, 203, 173) ' Light orange
Case 4
GetColor = RGB(214, 227, 188) ' Light yellow-green
Case 5
GetColor = RGB(234, 209, 220) ' Light pink
Case 6
GetColor = RGB(204, 192, 218) ' Light purple-blue
Case 7
GetColor = RGB(244, 176, 202) ' Light pink-red
Case 8
GetColor = RGB(201, 218, 248) ' Light sky blue
Case 9
GetColor = RGB(209, 227, 245) ' Light powder blue
End Select
End Function
edit: removed pasterange and colorindex vars that aren't used any longer
Bookmarks