Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Macro Pivot table where the range of data always changes

    Hi All

    I'm new to here and fairly new to VB so any help would be greatly appreciated

    I have a sheet with data that can change each day so the range of data will always be different. The columns and headers will always stay the same. But I might have data on row 5 todays and tomorrow it might be row 30.

    I have the below code which is recorded using the macro, how would l change the code to always find the last row and then create the macro.

    After this, I would like it to then place the pivot on the same sheet as I am working on but in a location which is 5 lines below the last piece of data in column C.

    Any help would be much appreciated

    Code:
    Sub statementpivot()
    '
    ' statementpivot Macro
    '
    
    Range("G35").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'1596'!R1C1:R24C45", Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="'[Global Extract - 30-11-2012.XLS]1596'!R35C7", _
    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("SITE NAME")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("OPEN AMOUNT GBP SUM"), "Sum of OPEN AMOUNT GBP SUM" _
    , xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("DUE DATE")
    .Orientation = xlRowField
    .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("OPEN AMOUNT SUM"), "Sum of OPEN AMOUNT SUM", xlSum
    With ActiveSheet.PivotTables("PivotTable2").DataPivotField
    .Orientation = xlColumnField
    .Position = 1
    End With
    End Sub
    Last edited by RetiredGeek; 2012-12-04 at 10:03. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    stimpsond1,

    Welcome to the Lounge as a new poster!

    What you want is a Dynamic Range Name.
    If you define your source data using this method it will automatically adjust as you add/delete rows of data.
    You can use the Count method to determine the number of rows in the Range and then calculate the + 5 rows for the placement of the Pivot Table since you know the starting position of the data.

    I've included the link to Microsoft's article on Dynamic Range Names but you can google that term and get many more you can also search here for many previous discussions on the topic.

    P.S. I've added code tags - [code] your code here [/code] to your code. Code tags make it easier for responders to copy your code for testing and preserve indentations in the code.
    Last edited by RetiredGeek; 2012-12-04 at 10:13.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    As an alternative since you are using code anyway:
    Code:
    Sub statementpivot()'
    ' statementpivot Macro
    '
       Dim pc                     As PivotCache
       Dim pt                     As PivotTable
       Dim lRow                   As Long
       Dim sAddress               As String
    
    
       ' output row for pivot table
       lRow = Sheets("1596").Cells(1, 1).CurrentRegion.Rows.Count + 5
    
    
       ' address of data range in R1C1 format
       sAddress = Sheets("1596").Cells(1, 1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    
    
       Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:="'1596'!" & sAddress, Version:=xlPivotTableVersion10)
    
    
       Set pt = pc.CreatePivotTable(TableDestination:="'[Global Extract - 30-11-2012.XLS]1596'!R" & lRow & "C3", _
                                    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10)
       With pt
          With .PivotFields("SITE NAME")
             .Orientation = xlRowField
             .Position = 1
          End With
          .AddDataField .PivotFields("OPEN AMOUNT GBP SUM"), "Sum of OPEN AMOUNT GBP SUM", xlSum
          With .PivotFields("DUE DATE")
             .Orientation = xlRowField
             .Position = 2
          End With
          .AddDataField .PivotFields("OPEN AMOUNT SUM"), "Sum of OPEN AMOUNT SUM", xlSum
          With .DataPivotField
             .Orientation = xlColumnField
             .Position = 1
          End With
       End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    My quick and dirty way at the time of the definition of the pivot table is to redefine the table in terms of columns like $A:$Z if you have 26 columns, You click of change data source in the pivot table options.
    Last edited by r3x3; 2012-12-04 at 22:17. Reason: need to add a phase.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It is not generally recommended to use entire columns as it uses more memory than necessary and introduces blank rows into the data, which also prevents the use of features like grouping dates. If those are not concerns it should be fine but there are more robust methods like the dynamic named ranges mentioned.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Appreicate your help guys, I'm not getting anywhere fast with this but will continue to try and crack it. Thanks for a quick responce

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What problem are you having with it currently?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Iím really new to VBA so I guess not knowing the syntax is difficult, I want to go on a training course as I think it is so powerful and should be utilised more.

    I have collated the code below from reading various websites and it seem to work, I plan to change the sheet name of 14051 with my msgbox that is input on the original sheet. (Currently I have split the macro into 2 to ensure it works) so I see no isses with replacing the sheet name.

    Iím trying to estabalish the last line of column C, and then offset that by 5 and let the pivot table always place itself there.

    I think it is something like this:

    Dim lastline

    Range("C:C").Select
    Selection.End(xlDown).Select
    lastline = ActiveCell.Row
    Selection.Offset(5, 0).Select

    TableDestination:="14051!lastline", TableName:="PivotTable2" _

    The above ď14051!lastlineĒ doesnít work



    Sub statementpivot()
    '
    ' statementpivot Macro

    Dim myRange As Range
    Range("A1").Select
    Set myRange = ActiveCell.CurrentRegion

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:=myRange, Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="14051!R100C5", TableName:="PivotTable2" _

    ActiveWorkbook.ShowPivotTableFieldList = True


    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("SITE NAME")
    .Orientation = xlRowField
    .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("OPEN AMOUNT GBP SUM"), "Sum of OPEN AMOUNT GBP SUM" _
    , xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("DUE DATE")
    .Orientation = xlRowField
    .Position = 2
    End With


    ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("OPEN AMOUNT SUM"), "Sum of OPEN AMOUNT SUM", xlSum
    With ActiveSheet.PivotTables("PivotTable2").DataPivotFi eld
    .Orientation = xlColumnField
    .Position = 1
    End With
    End Sub

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Did the code I posted not do what you wanted?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    It didnt work, the debug showed this line was the error:

    Set pt = pc.CreatePivotTable(TableDestination:="'[Global Extract - 30-11-2012.XLS]1596'!R" & lRow & "C3", _
    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10)

    Thanks for looking and responding.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What was the actual error?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Rory,

    I think i cracked it. On your code you had the ' after 1596, i have now removed this and it works fine.

    Set pt = pc.CreatePivotTable(TableDestination:="'[Global Extract - 30-11-2012.XLS]1596'!R" & lRow & "C3", _


    Thanks so much

  13. #13
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Rory,

    If your not fed up of me by now i have one more question

    your code below works a treat, however, this is ensureing i always pop my data on sheeet 60, priot to this i have a msgbox where we enter a number and that then creates the sheet, the msgbox is Ans1.

    Is there a way to amend the code so whatever a user enters ni msgbox it would use that sheet for the pivot sorce data and place to place the data,

    Many thanks

    Sub statementpivot() '
    ' statementpivot Macro

    '
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim lRow As Long
    Dim sAddress As String


    ' output row for pivot table
    lRow = Sheets("60").Cells(1, 1).CurrentRegion.Rows.Count + 5


    ' address of data range in R1C1 format
    sAddress = Sheets("60").Cells(1, 1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)


    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, _
    SourceData:="'60'!" & sAddress, Version:=xlPivotTableVersion10)


    Set pt = pc.CreatePivotTable(TableDestination:="60!R" & lRow & "C3", _
    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10)
    With pt
    With .PivotFields("SITE NAME")
    .Orientation = xlRowField
    .Position = 1
    End With
    .AddDataField .PivotFields("OPEN AMOUNT GBP SUM"), "Sum of OPEN AMOUNT GBP SUM", xlSum
    With .PivotFields("DUE DATE")
    .Orientation = xlRowField
    .Position = 2
    End With
    .AddDataField .PivotFields("OPEN AMOUNT SUM"), "Sum of OPEN AMOUNT SUM", xlSum
    With .DataPivotField
    .Orientation = xlColumnField
    .Position = 1
    End With
    End With
    End Sub

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    Sub statementpivot(strSheet As String)   '' statementpivot Macro
    
    
    '
       Dim pc                     As PivotCache
       Dim pt                     As PivotTable
       Dim lRow                   As Long
       Dim sAddress               As String
       Dim wks                    As Worksheet
    
    
       Set wks = Sheets(strSheet)
    
    
       ' output row for pivot table
       lRow = wks.Cells(1, 1).CurrentRegion.Rows.Count + 5
    
    
    
    
       ' address of data range in R1C1 format
       sAddress = wks.Cells(1, 1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    
    
    
    
       Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:="'" & strSheet & "'!" & sAddress, Version:=xlPivotTableVersion10)
    
    
    
    
       Set pt = pc.CreatePivotTable(TableDestination:=strSheet & "!R" & lRow & "C3", _
                                    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10)
       With pt
          With .PivotFields("SITE NAME")
             .Orientation = xlRowField
             .Position = 1
          End With
          .AddDataField .PivotFields("OPEN AMOUNT GBP SUM"), "Sum of OPEN AMOUNT GBP SUM", xlSum
          With .PivotFields("DUE DATE")
             .Orientation = xlRowField
             .Position = 2
          End With
          .AddDataField .PivotFields("OPEN AMOUNT SUM"), "Sum of OPEN AMOUNT SUM", xlSum
          With .DataPivotField
             .Orientation = xlColumnField
             .Position = 1
          End With
       End With
    End Sub
    and when you call it, use:
    Code:
    Call statementpivot(Ans1)
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    I've never used a call command before and when i copy the above code into my macro it doesnt run, i dont know where i would put the call command, i assume at the end of my first macro but not sure. Should this all go in 1 macro?

    The top peice of my macro less all the formattingis:

    Sub statementstest()
    '


    Dim lastline
    Dim Ans1
    Dim c As Range
    Dim d As Range


    Ans1 = InputBox("What seibel account number do you wish to generate the statement for?")
    MsgBox "You will now see all open records for account " & Ans1 & "."


    Sheets("Global Extract").Select
    Range("A1").AutoFilter Field:=2, Criteria1:="=" & Ans1, _
    Operator:=xlAnd

    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets(Ans1).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Sheets.Add.Name = Ans1
    Sheets("Global Extract").Select
    Range("A2:AP1000").Copy
    Sheets(Ans1).Select

    It then goes onto the ANS1 sheet and formats loads of stuff that i think is irrelevnt to display here.

    I'm stuck with where to place your code.

    Regards,

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •