VBA help for Dynamic Print Range

[mwtharp]

There are about 700 slicers that I need to filter through to publish a PDF invoice. The invoice is a header and some information, followed by a pivot table at the bottom that shows the line items. The pivot table can be anywhere from 1 page to 100 pages depending on the invoice. I am trying to figure out how to have the print area changed each time the slicer is changed in my VBA. I am fairly novice to VBA, and have used Access in the past for invoicing, but client wants to use Excel for this one. I have two issues (or three or more) issues with the VBA so far:

1) The print area will grow but not shrink
2) I need to add an If to look in cell F7. I want the invoice to not be published if F7 = 0
3) I'm not certain my slicer selection is working, but it seems to

Any advice is appreciated!

here is what I have so far:

Sub Filterpublish()



Dim LastRow As Long
LastRow = Range("A:F").SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.PageSetup.PrintArea = "$A$2:$G$" & LastRow



'Invoice 1


ActiveWorkbook.SlicerCaches("Slicer_Guar_Name").ClearManualFilter

With ActiveWorkbook.SlicerCaches("Slicer_Guar_Name")
.SlicerItems("Invoice 1").Selected = True



ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"path for publishing here" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, ignorePrintAreas _
:=False, OpenAfterPublish:=False

End With


'Invoice 2
ActiveWorkbook.SlicerCaches("Slicer_Guar_Name").ClearManualFilter

With ActiveWorkbook.SlicerCaches("Slicer_Guar_Name")
.SlicerItems("Invoice 2").Selected = True


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"path for publishing here" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, ignorePrintAreas _
:=False, OpenAfterPublish:=False

End With

'
End Sub

 

[ZMyrrh]

I would consider using " LastRow = worksheetfunction.counta(range("a1:a100"))" that way it will count how many cells are being used. You may need to change the range but keep it in one column or you will run into problems. Hope this helps!

I may not have helped everything depending on other errors but this is what I saw could make it better.