Fetch all rows that meet criteria and display in new worksheet

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?

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.

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.SpecialCells(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(xlUp).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?