Macro/Formula to extra data from certain rows

[Guest]

I would like to pull the data from columns C and J only from rows where
column F reads "Closed" and with this data form a list in another worksheet
in the same workbook. Just to clarify, I don't want the data to disappear
from worksheet 1, just it should automatically populate in worksheet 2 when
cell F reads "Closed".
Is this possible?

Thank you very much,
Scott

 

[Guest]

To make it automatic would require an "event" macro, but I think you'd find
it easier to do on request, either by a macro or filtering. Use
Data/Filter/Autofilter, click on the dropdown in col F & select "Closed",
then click column C, use Alt/; (selects visible cells only), copy, paste into
the "new" sheet, go back & do the same with col J.
For a macro:
Sub FSaysClosed()
Set orig = Selection
Application.ScreenUpdating = False
Range("F1").CurrentRegion.Select
Selection.AutoFilter Field:=6, Criteria1:="Closed"
Range("D:I").EntireColumn.Hidden = True
Range("C2:J65536").SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1).PasteSpecial
Selection.AutoFilter
Cells.EntireColumn.Hidden = False
orig.Select
Application.ScreenUpdating = True
End Sub

 

[Guest]

Well, the idea is that I won't have to manually search every time I want to
know how many "Closed" files I have. I have a running log of all the files
and on worksheet 2 I'm trying to keep a running monthly log of "closed" ones.
I figured since the info is already in the first worksheet, there must be
some way to have that same info form a smaller log. I tried the macro. The
first time it worked, but then when I changed some data it gave me an error
message and highlighted the following line:

Selection.AutoFilter Field:=6, Criteria1:="Closed"

Also, do you think there would be a way to have this happen without having
to push buttons?

Thanks for all your help,
Scott