Hi Mathan,
I used column A to determine the number of rows that should be
included in the
pivottable range. And I used Row 1 to determine the number of columns.
I assume that the headers are all nicely valid--no duplicates and no
blanks,
right. And that you want Sum for each of those other fields.
Option Explicit
Sub Pivot_Table()
Dim myRng As Range
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
Set wks = Worksheets("Data")
With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRng = .Range("A1", .Cells(LastRow, LastCol))
ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"
ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreatePivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With
With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")
.PivotTables(1).AddFields RowFields:=RowFieldArray
For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol
With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub
Regards,
Vallinayagam
On Dec 9, 3:59 pm, Mathan <mathan4s...@gmail.com> wrote:
> Dear All,
>
> Could you please help me out in this?
>
> In attached file I need to select all the data from column "C" to "Y" into
> data field in pivot table using VBA code.
>
> It is very hard to drag all the items. e.g.) If we have more
> than 100 coulmns.
>
> Thanks in advance for your quick reply.
>
> Regards,
> Mathan
>
> Pivot.xls
> 211KViewDownload
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
