Copy coloured font Rows from multiple tabs into 1 sheet

Smiffy3594 1 Reputation point


Can anyone help I have multiple tabs in a excel document (e.g 580400 / 580401 / 580402 / 580403) and some of the entry lines in each tab I have in blue colour font.

I am trying to copy all the blue font lines to another tab called "Sheet2" within the same workbook

I have been able to make this work on one tab (580400) but struggling with including the other tabs (580401 / 580402 / 580403) how do I include the other tabs in this code ?

Sub CopyColouredFontTransactions()

Dim PeriodField As Range
Dim PeriodCell As Range
Dim Sheet1WS As Worksheet
Dim Sheet2WS As Worksheet

Dim x As Long

Set Sheet1WS = Worksheets("580400")
Set PeriodField = Sheet1WS.Range("A2", Sheet1WS.Range("A2").End(xlDown))
Set Sheet2WS = Worksheets("Sheet2")

For Each PeriodCell In PeriodField

If PeriodCell.Font.Color = RGB(0, 176, 240) Then  
    PeriodCell.Resize(1, 15).Copy Destination:= _  
        Sheet2WS.Range("A1").Offset(Sheet2WS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)  
End If  

Next PeriodCell


End Sub

0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Oskar Shon 866 Reputation points MVP

    Yes you can use loop but easer to use filter based on your color.
    Record this way and optimize your code.

    0 comments No comments