Move Colored rows to another sheet

Davinder · Aug 14, 2014

Hi All,

I have an excel workbook in which there are 5 sheets,

My routine task is to keep maintain a record of utility vendors with accounting date that is every Wednesday in our company

Generally what i do on every Wednesday is put total amount from invoice then invoice date from the same and then Accounting date for current Wednesday and highlight it with different color for future reference

Next step is copy colored rowss from vendor sheet and paste these rows to another sheet so that i can check which Wednesday how much i have entered.

Could you please provide me some formulas to transfer colored rows to another sheet automatically.

1. Sheet name "utility vendor" refer attached below and same format used in other sheets for other vendors,

	Vendor Code & GL Code	Invoice #	Account # & Amount to GL Code	Total Bill	Invoice Date	Accounting Date	Service Dates
API	ENE222	Ste100-07/14	64006				0613-0715
APD	045-00-6035		0.00
API	ENE222	B101-9/13	64007
APD	045-00-6036		0.00
API	ENE222	B102-5/14	64008				0430-0512
APD	045-00-6036		0.00
API	ENE222	B103-9/13	64009
APD	045-00-6036		0.00
API	ENE222	B104-9/13	64009
APD	045-00-6036		0.00
API	ENE222	B105-9/13	64011
APD	045-00-6036		0.00
API	ENE222	B106 - 9/13	64012
APD	045-00-6036		0.00
API	ENE222	B108-9/13	64014
APD	045-00-6036		0.00

anglais428 · Aug 14, 2014

what color are your rows?

Alan_P · Aug 14, 2014

Hi Davinder,

Give this a go... it will find cells in column A that are filled with the standard yellow colour then copy the entire row to a new workbook. Also there has to be no blank cells in column A...

Cheers,
Alan.

Code:

Sub CopyFilled2NewBook()
Dim NewBook As Workbook
Dim Current As Workbook
Dim i As Long
Dim NextRow As Variant
Set Current = ThisWorkbook
Set NewBook = Workbooks.Add
Current.Activate
i = 1
Do While Cells(i, 1).Value <> ""
    If Cells(i, 1).Interior.Color = 65535 Then
    Cells(i, 1).EntireRow.Copy
    
    NewBook.Activate
  
    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
 
    Range("A" & NextRow).Activate
    ActiveSheet.Paste
    Current.Activate
    End If
    
    i = i + 1
    
Loop
NewBook.Activate
End Sub

Davinder · Aug 18, 2014

anglais428 said:
what color are your rows?

First of all thanks,

I don't have any specific/standard color, we just color these rows for future reference

Michael M · Aug 18, 2014

Maybe change this line then

Code:

If Cells(i, 1).Interior.Color = 65535

to

Code:

If Cells(i, 1).Interior.Color <> 0

Davinder · Aug 18, 2014

HI Michael,

I have tried your code, now this is only creating the 1st row to new workbook

Michael M · Aug 18, 2014

Sorry, try

Code:

If Cells(i, 1).Interior.ColorIndex <> 0

Davinder · Aug 18, 2014

No, Not working.

Davinder · Aug 18, 2014

Michael M said:
Sorry, try

Code:

If Cells(i, 1).Interior.ColorIndex <> 0

Michael, could you please provide me your email ID, I will send you copy of workbook. In this way you can better understand what I want.

Thanks

Michael M · Aug 18, 2014

Is there any data in column "A" of the "Current" workbook

Move Colored rows to another sheet

Davinder

New Member

Excel Facts

anglais428

Well-known Member

Alan_P

Well-known Member

Davinder

New Member

Michael M

Well-known Member

Davinder

New Member

Michael M

Well-known Member

Davinder

New Member

Davinder

New Member

Michael M

Well-known Member

Similar threads

Forum statistics

Share this page

Move Colored rows to another sheet

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock