Copy data from multiple sheets into master sheet if column A value is "REQUESTED"

[surkdidat]

Please can someone help! I need a formula which will run across multiple tabs (10 in total - each with a different persons name on) to copy into the "COMBINED" tab if column A on an agents worksheet is "REQUESTED". So if in Abbies worksheet A2 is "REQUESTED" then I want it to copy that line to the master tab, if A3 states "ON HOLD" it will skip this line. A3 contains "REQUESTED" so then I want it to copy this line. I then need this to do the same for all sheets. I don't want any blank lines on the "COMBINED" tab.

Many thanks

 

[My Aswer Is This]

A couple question here. When you say:
"multiple tabs (10 in total -"

Do you mean all sheets in your workbook except for sheet named:"COMBINED"

So copy from all sheets in this workbook to sheet named "COMBINED"

Is this correct?

If this is not correct then you need to say sheets 2 to 11 or sheet 12 to 22

Sheets are numbered from left to right as to how they are shown on tab bar

Sheet 1 is in far left position and sheet 2 is next sheet to right.

If you would answer these questions we may be able to help you.

But you said:"I need a formula which will...."

I do not believe this can be done with a formula.

I believe you would need a Vba script.

 

[surkdidat]

Hi there
Yes, the master sheet is called "Combined", This is the first sheet in my workbook. The next ten sheets are individual agents names, i.e. "Abbie", "Beth", "Charlotte" etc.,
Apologies, typ (of sorts!) I know its VBA, its a Friday and put formula instead!!!

 

[My Aswer Is This]

Thy this:

Be sure sheet is named: "Combined"
And we are looking for "REQUESTED"

Sub Copy_Rows()
'Modified 1-12-18 5:45 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
    For i = 2 To 11
        Lastrow = Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
    
        With Sheets(i).Range("A1:A" & Lastrowa)
            .AutoFilter 1, "REQUESTED"
            .Offset(1).EntireRow.Copy Sheets("Combined").Cells(Lastrow, 1)
            .AutoFilter
        End With
    
    Next
Application.ScreenUpdating = True
End Sub

 

[surkdidat]

Thank you for this. I am logically working out the For 1=2 to 11 is the number of sheets in it. Is there anyway of making this value "x" so if new sheets are added at any point then it saves a code change?

Thy this:

Be sure sheet is named: "Combined"
And we are looking for "REQUESTED"

Sub Copy_Rows()
'Modified 1-12-18 5:45 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
    For i = 2 To 11
        Lastrow = Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
    
        With Sheets(i).Range("A1:A" & Lastrowa)
            .AutoFilter 1, "REQUESTED"
            .Offset(1).EntireRow.Copy Sheets("Combined").Cells(Lastrow, 1)
            .AutoFilter
        End With
    
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try this:

It looks in all sheets from sheet 2 to last sheet in workbook.
Will that work for you?

Sub Copy_Rows()
'Modified 1-12-18 6:15 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
    For i = 2 To Sheets.Count
        Lastrow = Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
    
        With Sheets(i).Range("A1:A" & Lastrowa)
            .AutoFilter 1, "REQUESTED"
            .Offset(1).EntireRow.Copy Sheets("Combined").Cells(Lastrow, 1)
            .AutoFilter
        End With
    
    Next
Application.ScreenUpdating = True
End Sub

 

[surkdidat]

Works perfectly, and think I understand all the logic, so thanks very much

 

[My Aswer Is This]

Works perfectly, and think I understand all the logic, so thanks very much

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.