Posts by yzhang316

    Hi, I am trying to create a macro code to automatically find a range within transactions for multiple excel worksheets. However, worksheets don't always have the same pivot items. For example, the VBA code below is trying to filter on Field 13 to find "Borrowed and Lent", but there is way to bypass it when the filtered item is not available and just resumes to the next line?


    [VBA]
    Sub FilterMissingItem


    Dim pt As PivotTable
    Dim pf As PivotField
    Dim Pi As PivotItem


    Sheets("GL").Select
    ActiveSheet.Range("$A$1:$AJ$1").AutoFilter Field:=13, Criteria1:= _
    "Borrowed and Lent"
    Sheets("Sheet1").Select
    Set pt = ActiveSheet.PivotTables("NetZero")
    Set pf = pt.PivotFields("PA_EXPENDITURE_ID")


    'pf.ClearAllFilters
    'pf.PivotFilters.Add xlValueIsBetween, pt.DataFields(1), -0.001, 0.001


    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]<0.001,RC[-1]>-0.001),2,""#N/A"")"
    'Range("C6").Select
    'ActiveCell.FormulaR1C1 = "2"
    Range("C5").Select
    Selection.AutoFill Destination:=Range("C5:C" & Cells(Rows.Count, "A").End(xlUp).Row)


    Sheet1.Select
    Range("A5:C5").Select
    Range("C5").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="MyTable3", RefersToR1C1:= _
    "=Sheet1!R5C1:R50000C3"


    Sheets("GL").Select
    ActiveSheet.Range("A1").AutoFilter Field:=24, Criteria1:="="
    'Range("X4").Select


    End Sub
    [/VBA]


    Thanks!
    Wayne

    Hi, I am tyring to create filter selection process using VBA macro, but for some reason, the value from a filtered field is not working. Please take a look at my code..




    When I filter on "B & L", it works. However, when I try to filter the "Purchase" and "Adjustment" I get an error MSG of "Run-time error'1004': Unable to set the Visible property of the PivotItem class.



    Please help..


    Thanks!!


    Moderator note: Please take a moment to review the rules regarding the use of code tags. I have added them for you this time, please use them in the future.

    Hi, I am trying to create a dynamic cell selection to perform calculations in filtered range. My current code is following:


    Code
    ActiveSheet.Range("$A$1:$AJ$1").AutoFilter Field:=13, Criteria1:= _
            "Sales Invoices"
        Range("Y5991").Select
        ActiveWindow.SmallScroll Down:=-6
        Range("Y5991").Select
        ActiveCell.FormulaR1C1 = "=RIGHT(LEFT(RC[9],FIND(""ns#: "",RC[9],1)+10),6)"
        Range("Y5991").Select
        Selection.FillDown


    Currently, I have the first selected cell Y5991 as my first result in the filtered range. I would like to replace it as a dynamic coding.


    Thanks!!


    Wayne