Run Time Error 5


I have a macro which I set up a week ago and was working fine.  Then today when I tried to run it I received the following errror.

Run-time Error '5'

It was happening in the code when the macro was changing the "currentpage" in the pivottable to another selection.  Can anyone please help me to know what to do to correct this?  See my code below.



Public Country(60), ProdType(60), Curr(60), ProdSegment(60)

Sub Print2PDF()

    Dim i, j, k As Integer
    Do While ActiveCell.Offset(i, 0).Value <> "STOP"
        If ActiveCell.Offset(i, 0).Value = "X" Then
            Country(j) = ActiveCell.Offset(i, 2).Value
            ProdType(j) = ActiveCell.Offset(i, 3).Value
            Curr(j) = ActiveCell.Offset(i, 4).Value
            ProdSegment(j) = ActiveCell.Offset(i, 5).Value
            i = i + 1
            j = j + 1
            i = i + 1
        End If

    j = 0
    k = Sheets.Count
    i = Sheets.Count
    Do While j <= 60
        If Country(j) <> "" Then
            Sheets("Pricing History Chart").Select
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Product").CurrentPage = Country(j)
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Product Type").CurrentPage = ProdType(j)
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Currency").CurrentPage = Curr(j)
            ActiveSheet.PivotTables("PivotTable3").PivotFields("Product Segment").CurrentPage = ProdSegment(j)
            Sheets("Print Chart").Copy After:=Sheets(k)
            Sheets("Pricing History Chart").Select
            Application.Goto Reference:="Print_Area"
            Sheets(k + 1).Select
            ActiveSheet.Name = k - 6
            k = k + 1
        End If
        j = j + 1
    ' Select Multiple Sheets - Optional False is to extend the current selection to include any previously selected objects and the specified object
    Sheets("Pricing Report").Select False
    Do While i < k
        ActiveWorkbook.Sheets(i + 1).Select False
        i = i + 1
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,2,""PDF-XChange 4.0 on Ne01:"",,,,FALSE)"
    'Delete the sheets that were added for PDF file
    ' Remove Multiple Selections
    Sheets("Pricing History Chart").Select
    'This statement clears the public variables

End Sub


Question Info

Last updated February 28, 2019 Views 925 Applies to:

You can also look in the below forums which is dedicated to macro and other customization. Might be helpful.


If the above was helpful please click "Vote as helpful". If it solves your problem click "Mark As Answered". Cheers !!!!!!!!!!
If you find this answer as helpful,MARK-HELPFUL,If fixes the issue MARK-ANSWERED....Cheers!!!

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.