Total filtered rows - Range of object Global failed

[tanyamc]

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

[Paul_Hossler]

Attaching a small sample workbook with sufficient data and the macro makes it much easier to see what might be wrong

[tanyamc]

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...
:(

[p45cal]

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.

[Aussiebear]

Try


iLastRow_Unique= ws_Unique.Cells(Rows.Count, "B").End(xlUp).Row

and:

Set UniqueRng = ws_unique.Range("B2:B" & iLasrRow_Unique

[p45cal]

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.

[tanyamc]

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.

[tanyamc]

Funded Program in each file (now that sample worklist is corrected) are the ones to match/filter. Thank you both for your help!

[p45cal]

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

[tanyamc]

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.

[p45cal]

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).

[tanyamc]

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.

[tanyamc]

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

[p45cal]

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

[tanyamc]

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.

[tanyamc]

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!

[tanyamc]

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

[p45cal]

You'd be safer with:
With ListObj.TotalsRowRange
.Interior.Color = vbYellow
With .Font
.Bold = True
.Name = "Calibri"
.Size = 16
End With
End With

[tanyamc]

Thank you! (Again!)