Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro/Formula to extra data from certain rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro/Formula to extra data from certain rows
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 "Scott Marcus" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro/Formula to extra data from certain rows
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 "Bob Umlas, Excel MVP" wrote: 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 "Scott Marcus" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting all rows of data that have a value in a particular column | Excel Discussion (Misc queries) | |||
resetting last cell | Excel Discussion (Misc queries) | |||
combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
Import data and keep duplicate rows of data | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions |