Hi All,
I need your help on the below code. The code I've written to create a pivot is working fine so far, but I need some help to modify it a bit, and this is where I'm stuck now.
Here I want to make the pivot range dynamic. I'm not able to make the source data dynamic. Please see below, this is what I'm using at present.
Again, the Pivot destination has to be hard coded with something like
. Unfortunately, this one is throwing an error, saying "Run-time error '1004'. Application-defined or object-defined error." while selecting below lines of code.
Any help would be appreciated.
Thanks in advance.
I need your help on the below code. The code I've written to create a pivot is working fine so far, but I need some help to modify it a bit, and this is where I'm stuck now.
Code:
Option Explicit
Sub testme1()
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet
Dim PT As PivotTable
Dim Pf As PivotField
Dim vRowFields() As Variant
Dim vDataFields() As Variant
vRowFields = Array("A", "B", "C", "D")
Set wks = Worksheets("Sheet1")
With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2 'skipping columns A
LastCol = 286
ReDim DataFieldArray(26 To 286)
For iCol = 26 To 286
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
[B]SourceData:=wks.Range("Contents")).CreatePivotTable _
TableDestination:=Sheets("Pivot").Range("A5"), _
[/B]TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With
With Sheets("Pivot")
.Activate
Range("A5").Select
.PivotTables("PivotTable1").AddFields RowFields:=vRowFields
Dim i As Integer
Dim iFieldMax As Integer
On Error Resume Next
' Find the number of PivotFields
iFieldMax = ActiveSheet.PivotTables("PivotTable1").PivotFields.Count
' Loop through the fields in the Pivot
For i = 1 To iFieldMax
With ActiveSheet.PivotTables("PivotTable1").PivotFields(i)
'Set subtotal calculation to nothing
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
Next i
For iCol = 26 To 286
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Function = xlSum
End With
Next iCol
With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(1).ColumnGrand = False
End With
End SubHere I want to make the pivot range dynamic. I'm not able to make the source data dynamic. Please see below, this is what I'm using at present.
Code:
[B]SourceData:=wks.Range("Contents")).CreatePivotTable [/B]Again, the Pivot destination has to be hard coded with something like
Code:
[B]TableDestination:=Sheets("Pivot").Range("A5"), [/B]
Code:
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=wks.Range("Contents")).CreatePivotTable _
TableDestination:=Sheets("Pivot").Range("A5"), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10Any help would be appreciated.
Thanks in advance.
