• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Copy cells, then change cell content

The below code copies any row where Column C = Brown.  I need to expand so that once it copies the rows to the next empty cell, it then changes Brown to Yellow,

Option Explicit

Sub copyRows()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim lastRow As Long
Dim i As Long

    Application.ScreenUpdating = False
    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    lastRow = wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    Set rng = wks.Range("C1:C" & lastRow)
    For Each r In rng
        If UCase(r.Value) = "BROWN" Then
            wks.Range("A" & r.Row & ":L" & r.Row).Copy 'copy A:L on blank cell column C to bottom of existing worksheet
            wks.Range("A" & lastRow + i + 1).PasteSpecial
            Application.CutCopyMode = False
            i = i + 1
        End If

    Next r
    MsgBox "Process Complete"
    Application.ScreenUpdating = True
End Sub
1 Solution
Saqib Husain, SyedEngineerCommented:

wks.Range("C" & lastRow + i + 1).value="Yellow"


Application.CutCopyMode = False
mato01Author Commented:
Perfect.  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now