Hey there! Sign in to join this conversationNew here? Join for free

Fetch all rows that meet criteria and display in new worksheet Watch

    • Thread Starter
    Offline

    0
    ReputationRep:
    I am trying to automate excel so that it allows me to show all records from another tab where a certain condition is met.

    I have a worksheet called 'CAT' which has all information so is therefore my source table. On another worksheet called 'Summary' I have a dropdown cell which contains dates in cell G3 e.g. 2011, 2012, 2013 etc. when I choose a year I would like for all information from the 'CAT' worksheet that matches the date to be displayed in the 'Summary' worksheet starting in Cell D12, if that makes sense... I'll attach some screen shots so it makes it a bit easier to understand.

    So far I have tried some VBA, and put into Cell D12 - MyFunction(G3)

    Function MyFunction(parVal As String) As String
    'Clear what is there now
    ActiveSheet.Range("D12", "I3000").ClearContents

    'Add new rows
    varRange = Sheets("CAT").UsedRange
    varCount = 2
    For varRow = 1 To varRange.Rows.Count
    If varRange(varRow, 1) = parVal Then
    varRange(varRow, 1).EntireRow.Copy
    varCount = varCount + 1
    ActiveSheet.Cells(varCount, 1).EntireRow.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
    Next varRow
    MyFunction = "Found " & (varCount - 2) & " rows"
    End Function
    Attached Images
      
    • Study Helper
    Offline

    16
    ReputationRep:
    (Original post by Nathant93)
    I am trying to automate excel so that it allows me to show all records from another tab where a certain condition is met.

    I have a worksheet called 'CAT' which has all information so is therefore my source table. On another worksheet called 'Summary' I have a dropdown cell which contains dates in cell G3 e.g. 2011, 2012, 2013 etc. when I choose a year I would like for all information from the 'CAT' worksheet that matches the date to be displayed in the 'Summary' worksheet starting in Cell D12, if that makes sense... I'll attach some screen shots so it makes it a bit easier to understand.

    So far I have tried some VBA, and put into Cell D12 - MyFunction(G3)

    Function MyFunction(parVal As String) As String
    'Clear what is there now
    ActiveSheet.Range("D12", "I3000").ClearContents

    'Add new rows
    varRange = Sheets("CAT").UsedRange
    varCount = 2
    For varRow = 1 To varRange.Rows.Count
    If varRange(varRow, 1) = parVal Then
    varRange(varRow, 1).EntireRow.Copy
    varCount = varCount + 1
    ActiveSheet.Cells(varCount, 1).EntireRow.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
    Next varRow
    MyFunction = "Found " & (varCount - 2) & " rows"
    End Function
    You haven't actually said what your problem is!

    Does your code work, or are you running into some problems?
    • Thread Starter
    Offline

    0
    ReputationRep:
    (Original post by davros)
    You haven't actually said what your problem is!

    Does your code work, or are you running into some problems?
    It doesn't work at the moment, hence why it's on here Basically I have created the function but it doesn't do anything... On the 'Summary' worksheet I want to show all data from the 'CAT' worksheet when a single year is selected in the dropdown list in cell G3, so for example if i choose 2011 all the data from the CAT worksheet with the date of 2011 then the entire row is displayed in the 'Summary' sheet starting off from cell D12 onwards.
    • Study Helper
    Offline

    16
    ReputationRep:
    (Original post by Nathant93)
    It doesn't work at the moment, hence why it's on here Basically I have created the function but it doesn't do anything... On the 'Summary' worksheet I want to show all data from the 'CAT' worksheet when a single year is selected in the dropdown list in cell G3, so for example if i choose 2011 all the data from the CAT worksheet with the date of 2011 then the entire row is displayed in the 'Summary' sheet starting off from cell D12 onwards.
    OK, the thing that jumped out at me immediately when I scanned your code is that you have a function changing (or attempting to change) a worksheet!

    I'm not sure if it's technically illegal for a function to try to change cells on a sheet but it's certainly considered bad programming practice. Try changing your Function to a Sub (and your End Function to an End Sub) and also where you assign a value to the function at the end either use a MsgBox temporarily to report the number of found rows, or write the number of found rows to a specific cell.

    Run the macro from the Developer ribbon to start with - if it works then you'll need something like a command button on the worksheet that you can click to invoke the macro when it's finalized.

    See if this works - I'm pretty sure your problem is because worksheet changes are disallowed inside functions; the purpose of a function is to do calculations and return a result.
    • Thread Starter
    Offline

    0
    ReputationRep:
    (Original post by davros)
    OK, the thing that jumped out at me immediately when I scanned your code is that you have a function changing (or attempting to change) a worksheet!

    I'm not sure if it's technically illegal for a function to try to change cells on a sheet but it's certainly considered bad programming practice. Try changing your Function to a Sub (and your End Function to an End Sub) and also where you assign a value to the function at the end either use a MsgBox temporarily to report the number of found rows, or write the number of found rows to a specific cell.

    Run the macro from the Developer ribbon to start with - if it works then you'll need something like a command button on the worksheet that you can click to invoke the macro when it's finalized.

    See if this works - I'm pretty sure your problem is because worksheet changes are disallowed inside functions; the purpose of a function is to do calculations and return a result.
    I've given up using that code as it wasn't working so I created some more instead and it seems to work sort of but not quite, basically at the moment I am working on one sheet and trying to get it to work on that and then I'll try and get it to work over two sheets (if that makes sense), however it doesn't paste into the cell that I want it to start in and I don't know why, so was wondering if you could help with that?

    Here is my updated code....

    Sub FindDate()
    Dim Year As String
    Dim finalrow As Long
    Dim i As Integer

    'Clears the contents of Cells from N11 to Z321 on Sheet named CAT
    Sheets("CAT").Range("N11:Z321"). ClearContents

    'Gets the value of the year from Cell L4 on sheet named CAT
    Year = Sheets("CAT").Range("L4").Value


    finalrow = Sheets("CAT").UsedRange.SpecialC ells(xlCellTypeLastCell).Row
    MsgBox (finalrow)
    For i = 11 To finalrow
    'MsgBox (i)
    If Cells(i, 3) = Year Then
    Range(Cells(i, 1), Cells(i, 10)).Copy

    'Pastes Data into sheet CAT, starting at Cell X55
    Sheets("CAT").Range("X55").End(x lUp).Offset(1, 0).PasteSpecial
    End If
    Next i

    End Sub
    • Study Helper
    Offline

    16
    ReputationRep:
    (Original post by Nathant93)
    I've given up using that code as it wasn't working so I created some more instead and it seems to work sort of but not quite, basically at the moment I am working on one sheet and trying to get it to work on that and then I'll try and get it to work over two sheets (if that makes sense), however it doesn't paste into the cell that I want it to start in and I don't know why, so was wondering if you could help with that?

    Here is my updated code....

    Sub FindDate()
    Dim Year As String
    Dim finalrow As Long
    Dim i As Integer

    'Clears the contents of Cells from N11 to Z321 on Sheet named CAT
    Sheets("CAT").Range("N11:Z321"). ClearContents

    'Gets the value of the year from Cell L4 on sheet named CAT
    Year = Sheets("CAT").Range("L4").Value


    finalrow = Sheets("CAT").UsedRange.SpecialC ells(xlCellTypeLastCell).Row
    MsgBox (finalrow)
    For i = 11 To finalrow
    'MsgBox (i)
    If Cells(i, 3) = Year Then
    Range(Cells(i, 1), Cells(i, 10)).Copy

    'Pastes Data into sheet CAT, starting at Cell X55
    Sheets("CAT").Range("X55").End(x lUp).Offset(1, 0).PasteSpecial
    End If
    Next i

    End Sub
    Personally when I'm trying to do something like this I maintain a "target row" counter to keep track of the current row where I'm pasting (which won't be the same as the source row since not all rows get copied).

    So I'd write something like
    Dim targetRow as long

    targetRow = 55

    ' When there's a match, paste the result and increment targetRow:
    blah.cells(targetRow, 24).PasteSpecial ' 24 = column X
    targetRow = targetRow + 1

    Is anything being copied at all or does it go into the wrong place?
    • Thread Starter
    Offline

    0
    ReputationRep:
    (Original post by davros)
    Personally when I'm trying to do something like this I maintain a "target row" counter to keep track of the current row where I'm pasting (which won't be the same as the source row since not all rows get copied).

    So I'd write something like
    Dim targetRow as long

    targetRow = 55

    ' When there's a match, paste the result and increment targetRow:
    blah.cells(targetRow, 24).PasteSpecial ' 24 = column X
    targetRow = targetRow + 1

    Is anything being copied at all or does it go into the wrong place?
    The correct things are being copied but they're just being pasted into the wrong starting cell.. It must have something to do with the line:
    Sheets("CAT").Range("N55").End(x lUp).Offset(1, 0).PasteSpecial

    Cause it starts to paste them into cell N3? which I really don't understand
    • Thread Starter
    Offline

    0
    ReputationRep:
    Don't worry I have sorted it out...
 
 
 
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • Poll
    Will you be richer or poorer than your parents?
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

    Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

    Quick reply
    Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.