I have a spreadsheet in which I use a macro to create reports by a unique project number. This is done by generating a master file for all projects. This list creates a subtotal as the last row of the report. It automatically updates when you filter by project.
However when I run the reports for all projects, it does not include the subtotal line. Is their a routine that can be run to advise it to always include that line?
Here is the coding I have. Ideally in this section, I want to copy the project number to the line below what it finds so that it includes the subtotal prior to pasting to another sheet. Currently there are 96000 lines and each project number of lines vary. The subtotals currently always the last row, however we filter on the project number. No other filterable field is a viable option. When the project filters it copies only the visible lines to a new spreadsheet. It does this for each project and saves it as a unique xlsx file.
I need the file to add the subtotals that is automatically hidden when the file filters. It could simply copy the project number to last row (overwrite each time a new project is run) and refilter. But I cannot get that to work. Please advise. I have included the portion of the coding below that I have working
'Filter and copy it to the ProjectSegments sheet
With ThisWorkbook
.Sheets("QueryResults").Range("$A:$AY").AutoFilter Field:=1, Criteria1:=Numb
If Choice <> "Robert" Then
.Sheets("QueryResults").Range("$A:$AY").AutoFilter Field:=2, Criteria1:="<>BL"
End If
.Sheets("QueryResults").UsedRange.SpecialCells(xlCellTypeVisible).Copy
.Sheets("ProjectSegments").Range("A1").PasteSpecial xlPasteAll
Bookmarks