copy rows from multiple tabs with a criteria to another tab

Hi

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 (NOT CONDITIONAL FORMATTING)

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 ? I am new to VBA's

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

Sheet2WS.Columns.AutoFit

End Sub

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

You probably don't to loop through the cells. You should be able to filter on blue text, then select and copy in one go.

OK, try this. I have used your code rather than AutoFiltering as you don't have any filters set.

Please Login or Register  to view this content.

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

For future reference...Option making use of filter...

Please Login or Register  to view this content.