Returning data from multiple work sheets

[bnpdkh]

Hello all, I have been searching for a solution to a problem and am not able to find the solution. I have a workbook with multiple sheets that are set up exactly the same. I am adding a work sheet called "Added Summary". What I am trying to do is look at work sheet "t-12" search column IR9:IR306 for the word "New". For every row where "new"is found I want to return data contained in columns A:U to "Added Summary". I then need to search and return from sheets T-9, T-5,and T-4. Any help would be greatly appreciated and save alot of manual copying and pasting.

 

[Nebu]

Can you please upload a sample file..

 

[Smallman]

In the mean time, here is something to go on with.

Option Explicit
 
Sub Consolidate()
Dim ws As Worksheet
 
    For Each ws In Sheets(Array("T-12", "T-9", "T-5", "T-4"))
        ws.[W8:W309].AutoFilter 1, "New"
        ws.[A9:U309].Copy Sheet5.Range("A65536").End(xlUp)(2)
        ws.[w8].AutoFilter
    Next ws
End Sub

File attached to show workings. I simplified the data so it looks in Col W which I can see on a screen rather than looking 2 hungie columns away.

You can modify this for your needs.

Take care

Smallman

 

[bnpdkh]

Thanks Smallman, really appreciate the help. This is working perfectly as long as "New" is present in the designated area of the worksheets. If "New" is not present, which may happen, the macro is returning all 309 rows that are blank. Any ideas on what needs to be added to stop macro id "New" is not in designated area of worksheet? I should have been more clear in original post.
Also, what is the code below doing?

ws.[w8].AutoFilter

 

[Smallman]

Hi

When this occurs you need to create a trap for the last used row and execute the code only if the last row is greater than row 8, so:

Sub Consolidate()
Dim ws As Worksheet
Dim lr As Long
 
    For Each ws In Sheets(Array("T-12", "T-9", "T-5", "T-4"))
        ws.[W8:W309].AutoFilter 1, "New"
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        If lr > 8 Then
            ws.[A9:U309].Copy Sheet5.Range("A65536").End(xlUp)(2)
            ws.[w8].AutoFilter
        End If
    Next ws
End Sub

should get you over the line.

the

ws.[w8].autofilter

just turns the filter off after the procedure.

A more accurate way to write it would be as follows.

ws.autofiltermode = false

with the procedure starting with this line (in case there is a filter already on) and ending with this line, to turn filters off.

Hope that helps.

Take care

Smallman

 

[bnpdkh]

Sorry it has been awhile, but i just wanted to say thanks Smallman, this is working perfectly. Appreciate the help and patience, this has saved me considerable time. How do I mark this thread as solved?

 

[Smallman]

Very nice of you to come back and say thanks. It was a pleasure.

Take care

Smallman