Consulting

Results 1 to 19 of 19

Thread: Total filtered rows - Range of object Global failed

  1. #1
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location

    Question Total filtered rows - Range of object Global failed

    Goal: Use a worklist (unique range) to filter a data file (datarange), add a total of the matching rows, and export to PDF.

    Most everything I've tried (sum, subtotal, show totals, and more over the last few days) does not work. I get " Method 'Range' of object'_Global' failed ". (I was getting this error on another piece of the puzzle - ignoring blanks in the worklist - and I researched the error for that task and this one to no avail. I just made separate lists without the blanks to get around it.)

    Below is the section of my code where I get the error/have been testing solutions. The TESTING section is my latest attempt.

    Thanks.


    With ws_SD         
                'All rows - Range includes headers - there is a blank row and column in the raw data file
            Set DataRange = ws_SD.Range("$B$2:$O$" & iLastRow)
     
                'autofilter field is 5 as I want to print based on matching WBS value in column E
    
    
            DataRange.AutoFilter Field:=5
                'this puts the filter on the data range
                
    'Totals are turned on when filter applied
    
    
            Set UniqueRng = ws_unique.Range("A2:A" & iLastRow_unique) 'this range is only the WBS column in the worklist
            
            For Each Cell In UniqueRng 'worklist
                DataRange.AutoFilter Field:=4, Criteria1:=Cell 'filter spending details by WBS
                   'this changes visible rows but not row count
    
    
    'Totals disappear after filtering
    
    
    'TESTING START
    
    
    'ADD total to column O and formatting (yellow, bold, currency) to cell
    
    
    'ShowTotals = True 'doesn't work
    
    
    'below doesn't work because it is not recognizing the range set above? Range of object Global failed error
    
    
    Range(DataRange).Select
    ActiveCell.Formula2R1C1 = "subtotal(9,R[(iLastRow - 3)C:R[-1]C)"
    Range(iLastRow + 1).Select
    
    
    'format total to yellow, bold, currency
    
    
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColor = 16777215
            .Color = 65535
        End With
        
        With Selection
                .Font.Bold = True
                .Style = "Currency"
        End With
    
    
    'TESTING END
    Last edited by tanyamc; 10-21-2021 at 03:31 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,040
    Location
    Attaching a small sample workbook with sufficient data and the macro makes it much easier to see what might be wrong
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    I created a sample using limited data and a replica worklist, but it doesn't work like the original. No PDFs are created. My original will create the PDFs but I need to add a totals row, formatted, to each PDF. Even if the total was above the table if can't be done at the bottom of table. And it is protecting the sheet in my source data for some reason...

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    re:
        iLastRow_unique = ws_unique.Cells(Rows.Count, "A").End(xlUp).Row
    and:
    Set UniqueRng = ws_unique.Range("A2:A" & iLastRow_unique)
    There is nothing in column A.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,902
    Location
    Try
    iLastRow_Unique= ws_Unique.Cells(Rows.Count, "B").End(xlUp).Row
    and:
    Set UniqueRng = ws_unique.Range("B2:B" & iLasrRow_Unique
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    That would make the unique list be:
    3300002243
    3600023724
    but there's nothing resembling those numbers in any column to filter for.
    We need to know from the OP what columns to use - the names he's used at the top of the columns would be a good start.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location

    Corrected sample worklist file

    Unique range refers to the worklist file that has the two records in it. I missed a column (A) when I pasted the info into the sample. That would be why the sample didn't work but the original does.
    Attached Files Attached Files

  8. #8
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Funded Program in each file (now that sample worklist is corrected) are the ones to match/filter. Thank you both for your help!

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    That's better, now I get two pdfs produced. Now to the Totals problem.
    Excel has an irritating habit of extending the range you want it to autofilter; even in the line:
    DataRange.AutoFilter Field:=5
    where DataRange does NOT include the Totals line, after that line has executed the autofilter range DOES include the Totals row.

    This could be solved by changing the table on the Spending Detail Report sheet to a proper Excel Table:
    Delete the Totals line you had
    Select a cell in the remaining table and in the ribbon, Insert Tab, Tables section, choose Table.
    Then, while a cell is still selected in the new table, go to the Table Design tab of the ribbon and in the Table Style Option section, tick the Total row box.
    Now, as it happens, when I ran your code as it is, it did produce pdf files with totals.

    I don't advocate not changing the code, there's plenty to go wrong, but would a solution involving keeping that table as a proper Excel table be OK for tanymac?

    finally, re:
    And it is protecting the sheet in my source data for some reason...
    remove this line of code:
      .Protect Userinterfaceonly:=True, _
               DrawingObjects:=False, Contents:=True, Scenarios:= _
               True, AllowFormattingColumns:=True, AllowFormattingRows:=True
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Thank you for your response!

    The original raw data file includes a row that looks like a total row but is just data. I tried to replicate that in the sample. It is not a table prior to applying the filter. If the totals appeared for you it may be related to how I made the sample file since my original files don't work that way.

    I think, to do as you suggest, I would have to write a separate VBA piece to manipulate that file before running this PDF action on it. I'm not sure how to do that entirely but will experiment some more. Yesterday I tried adding a formula in Cell O1 to aggregate the column, but everything I tried either did nothing or errored out.

    The brief is to only use the worklist file to create the PDFs, with any manipulation of the raw data done in the background/unbeknownst to the user. The ultimate automation goal is that once a month the PDFs 'appear' in the proper fiscal year / fiscal month folder in our SharePoint site, completely unattended/independent of staff availability.

    Thanks for the help with the protection issue also.

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    Quote Originally Posted by tanyamc View Post
    It is not a table prior to applying the filter. If the totals appeared for you it may be related to how I made the sample file since my original files don't work that way.
    Nor is it a table after applying the autofilter. You need to make it into a table (a listobject) (take off the autofilter first - a proper Excel Table has its own filters).
    You need to
    Select a cell in the remaining table and in the ribbon, Insert Tab, Tables section, choose Table.
    If this table is never a proper Excel Table (ie never a listobject) one line in the existing macro can make it so (and add the total row). Nor would it be difficult to dump the original total row in the original macro too.
    If the original raw data absolutely must remain untouched, it would be very easy to make a (temporary) copy of that sheet and manipulate that (all in the same original macro).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Thanks. I tried turning it into a table after the auto filter, but it said it was already a table, so that's why I thought there was a connection.

    I'd like to open the raw data, manipulate it to run the PDFs, then close without saving.

    Could you show me how to change to code to make the table and then filter/total?

    Most of my attempts gave me object errors like it wasn't reading my ranges and I couldn't find any solutions. Thus why I posted here after several days of hitting walls on various methods.

  13. #13
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    I was able to convert the range to a table. But now when it filters the fake total row is showing. And I've tried to add the totals row but I keep getting errors (differ based on method).

    With ws_SD
            'I've set my range to reflect my headers which are fixed for this report
            Set DataRange = ws_SD.Range("$B$2:$O$" & iLastRow)
            '/////////iLastRow results is 2500 here, which excludes total row
     
            ws_SD.ListObjects.Add(SourceType:=xlSrcRange, Source:=DataRange).Name = "SD_Details"
            '////this makes it a table but excludes "fake" total row
     
            'autofilter field is 5 as I want to print based on the value in column E
            'DataRange.AutoFilter Field:=5
            
            '////need to add totals row to the table just created
            
     
            Set UniqueRng = ws_unique.Range("A2:A" & iLastRow_unique)
            For Each Cell In UniqueRng
                DataRange.AutoFilter Field:=4, Criteria1:=Cell
                
       '////this filters it but the 'fake' total row shows and is not the calculated total row

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    Try the code below.
    Note you'll need to update the code to reflect your workbooks' names and location (I've commented out yours so you'll just need to uncomment them and comment-out mine)
    Read the comments in the code, especially the one "the above line tries to open this workbook".
    The macro opens the xlsx file, but only long enough to make a copy of the Spending Detail Report sheet to a new workbook.
    All subsequent operations are on this copy which you can do what you want with because it won't be saved later.
    The totals row is deleted before the table is made into a proper Excel table, after which the table has its own total row added, which isn't hidden by the filtering.
    Then the filtering and pdf creation loop is run, after which the temporary new workbook is closed (without saving).
    Sub SDtoPDF()
    Dim DirectoryLocation
    Dim wbk_worklist As Workbook
    'same as workbook this macro lives in
    Dim ws_unique As Worksheet
    Dim wbk_SD As Workbook
    Dim ws_SD As Worksheet
    'Dim iLastRow As Long
    Dim iLastRow_unique As Long
    Dim UniqueRng As Range
    Dim Cell As Range
    'Dim LastRow As Long
    'Dim LastColumn As Long
    Dim sfldr As String
    Dim ListObj As ListObject, Name, totalcell As Range
    
    Application.ScreenUpdating = False
     
    'Note that the macro will save the pdf files in this active directory so you should save in an appropriate folder
    DirectoryLocation = ActiveWorkbook.Path
    'sfldr = "C:\Users\Tanya.Mcilravy\OneDrive - USDA\Documents\1Automation\SDPD\Separate Reports\"
    sfldr = "C:\Users\Public\Documents\"
        
    'Set wbk_SD = Workbooks.Open(fDialog.SelectedItems(1))
    'Set wbk_SD = Workbooks.Open(sfldr & "SAMPLE SD HANA_-_Spending_Detail_Report_NARST_-_FT_08_15_19.xlsx")
    'Set wbk_worklist = Workbooks.Open(sfldr & "SAMPLE SDPD worklist VBA.xlsm")
    Set wbk_SD = Workbooks.Open(sfldr & "vbaExpress69297SAMPLE SD HANA_-_Spending_Detail_Report_NARST_-_FT_08_15_19.xlsx")
    
    Set wbk_worklist = Workbooks.Open(sfldr & "vbaExpress69297SAMPLE SDPD worklist VBA.xlsm")
    'the above line tries to open this workbook (the one with the code in). It couldn't be running this macro without it being open so you can instead use:
    'Set wbk_worklist = ThisWorkbook
    
    'Set wbk_PD = Excel.Workbooks("ZANALYSIS_PATTERN PDF source")
    'add later
    Set ws_unique = wbk_worklist.Sheets("SD_List")
    iLastRow_unique = ws_unique.Cells(Rows.Count, "A").End(xlUp).Row
    Set UniqueRng = ws_unique.Range("A2:A" & iLastRow_unique)
    
    wbk_SD.Sheets("Spending Detail Report").Copy    'copies the sheet to a new workbook.
    Set ws_SD = ActiveSheet
    wbk_SD.Close (False)    'here the file is closed, left untouched.
    
    With ws_SD
      'to find the total row to delete it, either:
      Set totalcell = Intersect(.Range("B2").CurrentRegion, .Range("B:B")).Find("total", LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False, searchformat:=False)
      If Not totalcell Is Nothing Then totalcell.EntireRow.Delete
      
      'or:
      '********************************
      '  With .Range("B2").CurrentRegion
      '    'or .find in that column?
      '    If InStr(1, .Cells(.Rows.Count, 1).Value, "total", vbTextCompare) > 0 Then .Cells(.Rows.Count, 1).EntireRow.Delete
      '  End With
      '*********************************
      If .FilterMode Then .ShowAllData
    
      Set ListObj = .ListObjects.Add(xlSrcRange, .Range("B2").CurrentRegion, , xlYes) 'this makes a proper Excel table.
      ListObj.ShowTotals = True 'adds a total row
     
      'page setup here should WORK too...
      With .PageSetup
        .PrintArea = ListObj.Range.Address
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
      End With
      
      For Each Cell In UniqueRng
        ListObj.Range.AutoFilter Field:=4, Criteria1:=Cell
        Name = DirectoryLocation & "\" & Cell.Value & " _SD" & ".pdf"
        ws_SD.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      Next Cell
    End With
    ws_SD.Parent.Close False    'close the temporary new workook withut saving.
    Application.ScreenUpdating = True
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Thank you SO much. I am excited to give it a whirl and learn! Maybe some day I will know enough to answer someone's question in return.

  16. #16
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    It works on my original data files. Thank you!

    I am going to start a new thread as I have a couple more pieces I need help with and I want to follow the rules!

  17. #17
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Oh, and i was able to format the Totals row to match the brief

    Range("Table1[#Totals]").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Selection.Font.Bold = True
        With Selection.Font
            .Name = "Calibri"
            .Size = 16
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With

  18. #18
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    You'd be safer with:
      With ListObj.TotalsRowRange
        .Interior.Color = vbYellow
        With .Font
          .Bold = True
          .Name = "Calibri"
          .Size = 16
        End With
      End With
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    VBAX Regular
    Joined
    Oct 2021
    Posts
    14
    Location
    Thank you! (Again!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •