Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter macro doesn't like link
This macro works by taking data from worksheet1 called "Details" and
filtering it for a report on worksheet2 called "Summary." Works great. BUT! I changed the Details worksheet to become a link from another workbook. Now this macro doesn't work. How do I maintain the link while allowing this macro to work? Thanks! Dave Option Explicit Sub Summary() Dim lastrow As Long, path As String, today As Long, Ans As Variant Application.ScreenUpdating = False Sheets("Summary").Select Rows("2:65536").ClearContents Sheets("Details").Select lastrow = Cells(65536, 1).End(xlUp).Row Range(Cells(2, 5), Cells(lastrow, 5)).Select Selection.FormulaR1C1 = "=DATEVALUE(MID(RC[-3],FIND("" "",RC[-3],FIND("" "",RC[-3])+1 +1)+1,FIND("","",RC[-3],FIND("" "",RC[-3],FIND("" "",RC[-3])+1+1))- FIND("" "",RC[-3],FIND("" "",RC[-3])+1+1)-1) & ""-"" & LEFT(MID(RC[-3],FIND("" "",RC[-3]) +1,FIND("" "",RC[-3],FIND("" "",RC[-3])+1+1)-FIND("" "",RC[-3])+1),3) & "" -"" & MID(RC[-3],FIND("","",RC[-3],FIND("" "",RC[-3],FIND("" "",RC[-3])+1+1))+2,FIND("" "",RC[-3],FIND("","",RC[-3],FIND("" "",RC [-3],FIND("" "",RC[-3])+1+1))+2)-FIND("","",RC[-3],FIND("" "",RC[-3],FIND("" "",RC[-3])+1 +1))-2))+TIMEVALUE(MID(RC[-3],FIND("" "",RC[-3],FIND("","",RC[-3],FIND("" "",RC[-3],FIND ("" "",RC[-3])+1+1))+2)+1,15))" Selection.NumberFormat = "mm/dd/yy hh:mm:ss AM/PM" Selection.Copy Cells(2, 2).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.NumberFormat = "mm/dd/yy hh:mm:ss AM/PM" With Selection ..HorizontalAlignment = xlCenter ..VerticalAlignment = xlBottom ..WrapText = False ..Orientation = 0 ..AddIndent = False ..IndentLevel = 0 ..ShrinkToFit = False ..ReadingOrder = xlContext ..MergeCells = False End With Columns("E:E").ClearContents Sheets("Summary").Select Range(Sheets("Details").Cells(3, 1), Sheets("Details").Cells(lastrow, 4)).Copy Cells(2, 1) lastrow = Cells(65536, 1).End(xlUp).Row Columns("A:E").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Range(Cells(2, 5), Cells(lastrow, 5)).FormulaR1C1 = "=IF(RC1<R[-1]C1,""Y"",""N"")" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.Sort Key1:=Range("E2"), Order1:=xlDescending, Key2:=Range("A2") _ , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Ans = Application.CountIf(Columns("E"), "Y") Rows(Ans + 2 & ":" & 65536).ClearContents Columns("E").ClearContents ActiveWindow.ScrollRow = 2 Cells(2, 1).Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can this filter be done in a macro? | Excel Discussion (Misc queries) | |||
Do i use a filter or a macro? | Excel Discussion (Misc queries) | |||
How do I link filter results into another sheet? | Excel Worksheet Functions | |||
Is there a way to link Auto Filter values to a Cell | Excel Worksheet Functions | |||
Need a filter macro | Excel Discussion (Misc queries) |