0

I am trying to automate the number of pages to print, where a user inputs a set of data and it populates a sheet. Depending on how many items of data they add, the sheet I want to print could be 1 page, up to 3 pages.

So the sheet that presents this data has 3 copies of the same template.

I have a formula in a cell which works out how many pages to print.

Dim ToPrint As Integer

ToPrint = Sheets("Sheet7").Range("Too")
Application.ScreenUpdating = False
Sheets("CI").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\PDF Files\Export.pdf", _
        OpenAfterPublish:=True, _
        From:=1, _
        To:=ToPrint



Sheets("Sheet7").Select
Application.ScreenUpdating = True

The bit with ToPrint doesn't seem to be working as expected.

Range("Too") is reference to a single cell, which holds the formula, which is an if statement which can be 1, 2 or 3.

When I run it, I get an error. run time error -2147018887 (80071779).

Please can someone help me?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ceebee86
  • 13
  • 5
  • In which line do you get the error? • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jan 27 '20 at 12:55

1 Answers1

0

And...I worked it out.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\PDF Files\Export.pdf", _
        From:=1, _
        To:=ToPrint, _
        OpenAfterPublish:=True

Had to place OpenAfterPublish at the end. Referencing https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat

Ceebee86
  • 13
  • 5
  • If you give the parameters *by name* using `ParameterName:=Value` their order is irrelevant. More likely you had a typo or some other issue before. – Pᴇʜ Jan 27 '20 at 12:58
  • 1
    Instead of using `.Select` and `ActiveSheet` directly specify the sheet to export like `Sheets("CI").ExportAsFixedFormat …` much more reliable and a good coding practice. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jan 27 '20 at 13:01
  • 1
    Makes sense, I got rid of the Selects. :) – Ceebee86 Jan 27 '20 at 14:36