• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Private Sub Within A Sub Not Working :(

C

Christof

Member
Hi I was wondering if someone could help.
I have a report that runs some script to update pivots if a selection is made (cell $c$3).

I have tried to combine that with a script that will print out all selections from within a dropdown list.

Can anyone advise where I've gone wrong? Or can I do a sub within a sub?

Below is my code, the bits in bold is the bits I'm trying to add to make it print off all the reports I will need.

Oh and my file is here:
https://drive.google.com/open?id=1AiFQc7aTH8gQRHF0eGVCEL-7GnQ0kxu3


Cheers
Chris




Code: 
Sub Iterate_Through_data_Validation()
Dim dvCell As Range
Dim inputRange As Range
Dim c As Range


'Which cell has data validation
  Set dvCell = Worksheets("PRICING-SHEET").Range("C3")
'Determine where validation comes from
  Set inputRange = Evaluate(dvCell.Validation.Formula1)

For Each c In inputRange
  dvCell = c.Value



Private Sub Worksheet_Change(ByVal Target As Range)
  Dim KeyCells As Range
  Set KeyCells = Range("C3:AC3")
  If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
  Application.Calculation = xlManual

  'Set the Variables to be used
  Dim pt1 As PivotTable
  Dim pt2 As PivotTable
  Dim pt3 As PivotTable
  Dim pt4 As PivotTable
  Dim pt5 As PivotTable
  Dim pt6 As PivotTable
  Dim Field1 As PivotField
  Dim Field2 As PivotField
  Dim Field3 As PivotField
  Dim Field4 As PivotField
  Dim Field5 As PivotField
  Dim Field6 As PivotField
  Dim Field7 As PivotField
  Dim Field8 As PivotField
  Dim Field9 As PivotField
  Dim NewCat As String
  Dim NewCat2 As String
  Dim NewCat3 As Date
  Dim NewCat4 As String
  Dim pi As PivotItem

  'Here you amend to suit your data
  Set pt1 = Worksheets("PRICING-SHEET").PivotTables("PivotTable1")
  Set pt2 = Worksheets("PRICING-SHEET").PivotTables("PivotTable2")
  Set pt3 = Worksheets("PRICING-SHEET").PivotTables("PivotTable3")
  Set pt4 = Worksheets("PRICING-SHEET").PivotTables("PivotTable4")
  Set pt5 = Worksheets("PRICING-SHEET").PivotTables("PivotTable5")
  Set pt6 = Worksheets("PRICING-SHEET").PivotTables("PivotTable6")
  Set Field1 = pt1.PivotFields("Tour Code")
  Set Field2 = pt1.PivotFields("S/T")
  Set Field3 = pt2.PivotFields("Tour Code")
  Set Field4 = pt3.PivotFields("Tour Code")
  Set Field5 = pt4.PivotFields("Tour Code")
  Set Field6 = pt4.PivotFields("Download")
  Set Field7 = pt4.PivotFields("Dept Year")
  Set Field8 = pt5.PivotFields("Tour Code")
  Set Field9 = pt6.PivotFields("Tour Code")

  NewCat = Worksheets("PRICING-SHEET").Range("C3").Value
  NewCat2 = Worksheets("PRICING-SHEET").Range("AP33").Value
  NewCat3 = Worksheets("PRICING-SHEET").Range("AW11").Value
  NewCat4 = Worksheets("PRICING-SHEET").Range("z3").Value
  
  Field1.CurrentPage = NewCat
  Field2.CurrentPage = NewCat2
  pt1.RefreshTable

  Field3.CurrentPage = NewCat
  pt2.RefreshTable

  Field4.CurrentPage = NewCat
  pt3.RefreshTable

  Field5.CurrentPage = NewCat
  Field6.CurrentPage = NewCat3
  Field7.CurrentPage = NewCat4
  pt4.RefreshTable

  
  Field8.CurrentPage = NewCat
  pt5.RefreshTable
  
  Field9.CurrentPage = NewCat
  pt6.RefreshTable
  
  
  Application.Calculation = xlAutomatic
  
  End If

End Sub



  Sheets("PRICING-SHEET").PrintOut 'print to the default printer
Next c

End Sub
 
You can't embed one sub within another (well, you sort of can with a GoSub statement but that's pretty archaic). You can call one from the other but it's not really clear to me what you are trying to achieve here. As a rule, if you want to try and call a worksheet event (like Worksheet_Change) from another routine, you should move the code from the Change event into a routine in a normal module and then call that from both places.
 
Many thanks for your reply.
What I want to do run a macro that will print my main sheet "PRICING-SHEET", for each of the available selections in the dropdown box (C3).
However I need sub to run when cell C3 is changed so that the data on the page all updates.

Hope that is a bit clearer.
 
I don't really see the issue then. I assume your first code changes the cell on the sheet with the change event, at which point the change event will run, and then you print the sheet out. What is not happening that should be?
 
Christof
Could this work for Your case ( ... actually, this won't 'PrintOut' )?
I tried to make as less changes as possible ... compare to Your original.

Code: 
Sub Iterate_Through_data_Validation()
    Dim dvCell As Range
    Dim inputRange As Range
    Dim c As Range

'Which cell has data validation
    Set dvCell = Worksheets("PRICING-SHEET").Range("C3")
'Determine where validation comes from
    Set inputRange = Evaluate(dvCell.Validation.Formula1)

    Dim KeyCells As Range
    Set KeyCells = Range("C3:AC3")

    For Each c In inputRange
        With Application
            .Calculation = xlManual
            .ScreenUpdating = False
        End With
        dvCell = c.Value
        'Set the Variables to be used
        Dim pt1 As PivotTable
        Dim pt2 As PivotTable
        Dim pt3 As PivotTable
        Dim pt4 As PivotTable
        Dim pt5 As PivotTable
        Dim pt6 As PivotTable
        Dim Field1 As PivotField
        Dim Field2 As PivotField
        Dim Field3 As PivotField
        Dim Field4 As PivotField
        Dim Field5 As PivotField
        Dim Field6 As PivotField
        Dim Field7 As PivotField
        Dim Field8 As PivotField
        Dim Field9 As PivotField
        Dim NewCat As String
        Dim NewCat2 As String
        Dim NewCat3 As Date
        Dim NewCat4 As String
        Dim pi As PivotItem
        'Here you amend to suit your data
        Set pt1 = Worksheets("PRICING-SHEET").PivotTables("PivotTable1")
        Set pt2 = Worksheets("PRICING-SHEET").PivotTables("PivotTable2")
        Set pt3 = Worksheets("PRICING-SHEET").PivotTables("PivotTable3")
        Set pt4 = Worksheets("PRICING-SHEET").PivotTables("PivotTable4")
        Set pt5 = Worksheets("PRICING-SHEET").PivotTables("PivotTable5")
        Set pt6 = Worksheets("PRICING-SHEET").PivotTables("PivotTable6")
        Set Field1 = pt1.PivotFields("Tour Code")
        Set Field2 = pt1.PivotFields("S/T")
        Set Field3 = pt2.PivotFields("Tour Code")
        Set Field4 = pt3.PivotFields("Tour Code")
        Set Field5 = pt4.PivotFields("Tour Code")
        Set Field6 = pt4.PivotFields("Download")
        Set Field7 = pt4.PivotFields("Dept Year")
        Set Field8 = pt5.PivotFields("Tour Code")
        Set Field9 = pt6.PivotFields("Tour Code")
        NewCat = Worksheets("PRICING-SHEET").Range("C3").Value
        NewCat2 = Worksheets("PRICING-SHEET").Range("AP33").Value
        NewCat3 = Worksheets("PRICING-SHEET").Range("AW11").Value
        NewCat4 = Worksheets("PRICING-SHEET").Range("z3").Value
        Field1.CurrentPage = NewCat
        Field2.CurrentPage = NewCat2
        pt1.RefreshTable
        Field3.CurrentPage = NewCat
        pt2.RefreshTable
        Field4.CurrentPage = NewCat
        pt3.RefreshTable
        Field5.CurrentPage = NewCat
        Field6.CurrentPage = NewCat3
        Field7.CurrentPage = NewCat4
        pt4.RefreshTable
        Field8.CurrentPage = NewCat
        pt5.RefreshTable
        Field9.CurrentPage = NewCat
        pt6.RefreshTable
        Application.Calculation = xlAutomatic
       
        DoEvents    '   because I won't print this sheet !
'        Sheets("PRICING-SHEET").PrintOut 'print to the default printer
    Next c
End Sub
 
You must log in or register to reply here.
Back
Top