Excel help

PCDave

PCDave

PCDave

Associate
Joined
10 Nov 2007
Posts
809
Location
Southampton
ITs been a while since I have used Excel and cannot quite remember how to do this.

I have multiple sheets with data of the same value

e.g. sheet 1 columns- id - name - status
sheet 2 coloumns - id - name - status

etc

On the last sheet I want to pull all the rows from each sheet that match the status criteria of "yes" into one sheet so I can review them all.

Does anyone know a good way to do this?
 
You can use a filter, and then just copy your results onto the other sheet if you want?

Or are you wanting a solution which will update itself?
 
NutterzUK said:
You can use a filter, and then just copy your results onto the other sheet if you want?

Or are you wanting a solution which will update itself?
Click to expand...

A Solution which would update itself would be preferred. I have filters on them already if I need to find the data out manually but just as a small project want to get all the info I need in one place instead of going to about 15 different sheets each time and running filters...any ideas?
 
I think you'd be looking at Macros to do it for you, since there is no formula you can put in any one cell to pull information into more than one cell.

I can create you a macro if you'd like (Will have to be some time tomorrow, i'm off to bed soon), but then you will have to save it as a macro enabled document.
 
Ah here we go,.. someone else already made the same macro, that's usually the case.

Linky

You will need to adapt it with your sheet name though, or perhaps put another loop which will loop through your sheets.
 
I have found a formula which achives what I want but I am not sure how to configure it to work in mine.

Code: 
Sub Filter()
    With Sheet1
'
        .Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range( _
        "Criteria"), Unique:=False

        .Range("Data").SpecialCells(xlCellTypeVisible).Copy Destination:="PasteRange"
        .ShowAllData
    End With
End Sub

I know where it says "data" and "criteria i need to enter my values but I am unsure of what to put in. Any ideas?
 
I think for that to work you will need to name two ranges, one named "Data" and one named "Pasterange". You can do that in the upper left after highligting a lot of cells.

Does your document contain anything confidential?
 
Thanks for your help. It does unfortunately otherwise I would post it here.

A B C D E F
------------------------------------------------------------------------
1 ID Name Status ID Name Status
-------------------------------------------------------------------------
2 1 drink received Received
3 2 Food ordered Ordered
4 3 beer not ordered Not Ordered

Above I have made a mock spreadsheet to try and help me understand

Would the formula look something like this?

Code: 
Sub Filter()
    With Sheet1
'
        .Range("A1:C4").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range( _
        "D1:F4"), Unique:=False

        .Range("A1:C4").SpecialCells(xlCellTypeVisible).Copy Destination:="sheet2!$a$1:$c$4"
        .ShowAllData
    End With
End Sub
 
Try this. create a new module in vba and copy the code in.


Code: 
Sub buildOutput()

    Application.ScreenUpdating = False
    
    ' Column references
    Const colID As Integer = 1
    Const colName As Integer = 2
    Const colStatus As Integer = 3
    
    
    Dim sheetlastrow As Integer
    Dim OutRow As Integer
    Dim OutSht As Worksheet
    Dim sheet As Worksheet
    
    Dim x As Integer
    
    ' Change "output" to the name of your last sheet
    Set OutSht = ThisWorkbook.Worksheets("output")
    
    ' delete the previous data on the output sheet. remove this if not required
    OutSht.Range("a2:c1000").Delete
    
    ' Start on row 2 of the output sheet
    OutRow = 2
    
    For Each sheet In ThisWorkbook.Worksheets
        If sheet.Name = outsht.name Then
            'do nothing
        Else
            sheetlastrow = sheet.Range("A" & Rows.Count).End(xlUp).Row
            For x = 2 To sheetlastrow
                If UCase(sheet.Cells(x, 3).Value) = "YES" Then
                    OutSht.Cells(OutRow, colID).Value = sheet.Cells(x, colID).Value
                    OutSht.Cells(OutRow, colName).Value = sheet.Cells(x, colName).Value
                    OutSht.Cells(OutRow, colStatus).Value = sheet.Cells(x, colStatus).Value
                    OutRow = OutRow + 1
                End If
            Next x
        End If
    Next sheet
    
    Application.ScreenUpdating = True
End Sub


This works assuming each sheet only has 3 columns :)
 
Last edited:
Thanks to both of you for your help. With a bit of modding to the formula above I now have it working.
 
You must log in or register to reply here.
Back
Top Bottom