Macro Pivot table where the range of data always changes

Topic

New Reply

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

Reply | Quote

Replies

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.:cheers:

P.S. I’ve added code tags – [noparse]

Code:

 your code here 

[/noparse] to your code. Code tags make it easier for responders to copy your code for testing and preserve indentations in the code.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. 😉

Reply | Quote

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

Reply | Quote

What problem are you having with it currently?

Reply | Quote

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:=xlDatabase, 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”).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

Reply | Quote

Did the code I posted not do what you wanted?

Reply | Quote

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.

Reply | Quote

What was the actual error?

Reply | Quote

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

Reply | Quote

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:=xlDatabase, _
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

Reply | Quote

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)

Reply | Quote

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,

Reply | Quote

Does that routine not currently run the statement routine we have been discussing?

Reply | Quote