+ Reply to Thread
Results 1 to 2 of 2

Copy values from one range and paste in another with some slight modifications

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    245

    Copy values from one range and paste in another with some slight modifications

    So I'm trying to copy data from row 3 through 20 and paste it repetitively beginning at row 21 based on the value of an input box. However, with each paste iteration I want to:

    a) clear the pasted data in columns L & M
    b) increment the values in the column K by 1

    To illustrate what I'm looking for, I've attached my workbook which includes a sheet entitled 'Holes'. This worksheet displays the results of my code as it exists now (see below). The tab entitled 'What I want' displays the results I'm trying to achieve.

    
    Sub RepeatDataWithColorAndClearColumns()
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim repeatCount As Integer
        Dim i As Integer
        Dim colorIndex As Integer
        Dim pasteRange As Range
        
        ' 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)
        
        ' Find the last used row in column A
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Set starting row
        Dim startRow As Long
        startRow = 21
        
        ' Copy data from rows 3 through 20 and paste it repetitively
        For i = 1 To repeatCount
            ' Set paste range for each repetition
            Set pasteRange = ws.Rows(startRow).Resize(18, 13)
            
            ' Copy data and paste it in the paste range
            ws.Rows("3:20").Copy Destination:=pasteRange
            
            ' Shade the first row in the paste range from column 1 through 13 with a light color
            ShadeRange pasteRange.Rows(1), i
            
            ' Clear contents of columns 12 and 13 in the paste range starting from row 21
            pasteRange.Offset(1).Columns(12).Resize(pasteRange.Rows.Count - 1, 2).ClearContents
            
            ' Increment startRow by number of copied rows
            startRow = startRow + 18
        Next i
    End Sub
    
    Private Sub ShadeRange(rng As Range, ByVal colorIndex As Integer)
        ' Function to shade a range with a light color
        Dim cell As Range
        
        For Each cell In rng.Cells
            If cell.Column <= 13 Then ' Check if the cell is within columns 1 through 13
                cell.Interior.Color = GetColor(colorIndex)
            End If
        Next cell
    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-18-2023
    Location
    Geogia, USA
    MS-Off Ver
    365
    Posts
    133

    Re: Copy values from one range and paste in another with some slight modifications

    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
    Attached Files Attached Files
    Last edited by jdelano; 03-30-2024 at 09:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro to filter range then copy paste range only and insert as values.
    By Lukeb123 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-24-2021, 06:50 AM
  2. [SOLVED] Macro to copy cells from a selected range and just paste values back to that same range
    By catscats11 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-18-2019, 02:59 PM
  3. [SOLVED] Copy & Paste all unique values from a range
    By liamfrancis2013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2016, 07:09 AM
  4. Replies: 2
    Last Post: 09-16-2014, 10:13 AM
  5. [SOLVED] Copy and paste range as values to specific sheet/range
    By lukestkd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2014, 09:48 PM
  6. copy and paste a range of values
    By strucad in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-13-2012, 09:56 AM
  7. Slight modification to copy paste loop
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-19-2010, 04:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1