Automatic Page setup due to increase or decrease

[paradise2sr]

Hi,

I am using a pivot table very frequently whose rows increase and sometimes decreases.Hence,I have to accordingly do the setup for pagebreak and has drag the fill handler towards the of end data available.

How this can be resolved with vba.

Thanx

[sanju2323]

Please attachment sample your requirement

[paradise2sr]

Pls find enclosed in attachment.Currently in PT Sheet which is infact Pivot table whose page setup is A1:B8.When the data increases or decreases in data sheet ,the page setup in PT Sheet which once set remains there only i.e A1:B8.So,I want it to be dynamic with the increase or decrease in rows.

[sanju2323]

Sorry for the late reply I was very busy.
Try this

Sub Tester()
    Dim rng As Range, dict As Object
    With Sheets("Data").Activate

    Set rng = Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).Resize(, 3)

    Set dict = SubTotals(rng, 1, 3)
    
    DumpDict dict, Sheets("PT").Range("C4")
    Sheets("PT").Activate
End With
End Sub

Function SubTotals(rng As Range, colKey As Long, colVal As Long) As Object
    Dim rv As Object, rw As Range, k, v
    Set rv = CreateObject("scripting.dictionary")
    For Each rw In rng.Rows
        k = rw.Cells(colKey).Value
        v = rw.Cells(colVal).Value
        If Not IsError(k) And Not IsError(v) Then
            If Len(k) > 0 And IsNumeric(v) Then
                rv(k) = rv(k) + v
            End If
        End If
    Next rw
    Set SubTotals = rv
End Function

Sub DumpDict(dict As Object, rng As Range)
    Dim i As Long, k
    i = 0
    For Each k In dict.keys
        With rng.Cells(1)
            .Offset(i, 0).Value = k
            .Offset(i, 1).Value = dict(k)
        End With
        i = i + 1
    Next
End Sub

[paradise2sr]

Hi,
Its not working.I have used your code but when I increase the data the pagebreak does not changes.

Kindly see what is missing in the code.

[sanju2323]

Try this attachment file.

[paradise2sr]

Ok this now perhaps work.If possible can you mentioned the comment in each line of code you have mentioned for my reference.

I am marking this thread as solved.

Last but not least, can u make a pivot table with two different sheets based on condition which I have posted in a thread at >>http://www.excelforum.com/showthread...t=#post4339025

Thanx for your kind support.