CODEWITHSUNDEEP
vbaexcel
Sivasanth
Sivasanth

Reputation: 11

Excel VBA, Copying Colored Rows

I have a list in "Sheet1" with three Columns, A (Account Number), B (Description) & C (Amount). Based on 1st two columns (A & B) color, I want to copy the specific row to "Sheet2" and Paste it under one specific header (I have three headers).

Example

  1. Sheet1 - Cell A2 is "Red" & B2 is "Yellow", Copy/Paste Under Header "Inefficiencies" in Sheet2
  2. Sheet1 - Cell A3 is "Blue" & B3 is "No Color" Copy/Paste Under Header "Effective" in Sheet2
Account Number  Description  Amount
LP001022        Graduate     3,076.00 
LP001031        Graduate     5,000.00 
LP001035        Graduate     2,340.00

I have taken a code from this site already, but I could not completely configure it to my needs. Thank you for the help in advance. 

Sub lastrow()
    Dim lastrow As Long
    Dim i As Long, j As Long
    Dim acell As Range

    With Worksheets("Sheet1")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    MsgBox (lastrow)

    With Worksheets("Sheet3")
        j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    For i = 1 To lastrow
        With Worksheets("Sheet1")
            If .Cells(i, 1).Interior.Color = RGB(255, 255, 0) And _
               .Cells(i, 1).Interior.ColorIndex = xlNone Then
                   .Rows(i).Copy 'I have to give destination 
                   j = j + 1
                End If
        End With
    Next i
End Sub

Upvotes: 1

Views: 1032

Answers (1)

dcromley
dcromley

Reputation: 1410

Here are the key instructions to copy a row from sheet1 to INSERT into a row in sheet2. This assumes you have all the row numbers.

' -- to copy a row in sh1 to INSERT into sh2:
  sh2.Rows(irowInefficiency + 1).Insert
  sh1.Rows(irowFrom).Copy sh2.Rows(irowInefficiency + 1)
' -- you have to increment all header rows after this one
  irowEffective = irowEffective + 1

The following puts these in context:

Sub sub1() ' copy/insert a row
  Dim irowFrom&, irowInefficiency&, irowEffective&
  Dim sh1, sh2 As Worksheet
  Set sh1 = Sheets("sheet1")
  Set sh2 = Sheets("sheet2")
  irowInefficiency = 3 ' where that header is
  irowEffective = 6 ' where that header is
  irowFrom = 5 ' the row to copy
' -- to copy a row in sh1 to INSERT into sh2:
  sh2.Rows(irowInefficiency + 1).Insert ' a blank row
  sh1.Rows(irowFrom).Copy sh2.Rows(irowInefficiency + 1) ' then copy
' -- you have to increment all header rows after this one
  irowEffective = irowEffective + 1 ' because it increases
End Sub

Upvotes: 2

Related Questions