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 |
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 |
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 |
All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com