Copy a Range, changing values in one column from a list

Benntex · May 24th 2019

In the attached range, I want to copy the range below, changing the values in column C to each of the values in the list on sheet 2. The list will be different each time I run it.

Mumps · May 24th 2019

Try:

Code

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long, LastRow2 As Long, LastRow3 As Long, zone As Range, srcWS As Worksheet
    Set srcWS = Sheets("Sheet2")
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each zone In srcWS.Range("A1", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        LastRow2 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("B2:K" & LastRow).Copy Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        LastRow3 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("C" & LastRow2 + 1 & ":C" & LastRow3) = zone
    Next zone
    Application.ScreenUpdating = True
End Sub

Display More

Copy a Range, changing values in one column from a list

Participate now!

Share