Dragging multiple columns at a time in Pivot Wizard?

[seena]

Is there any easier way to drag multiple columns at one time. For Ex. If I have 5 years of data by month and If I had to drag 2 years of data I would have to drag 24 times. Is there any easy way to drag all 24 months in 1 go ? Sorry I did not add that this is while using the Pivot Wizard to create a pivot table.

 

[pbornemeier]

Welcome to the Board!

I do not know of any way to drag multiple rows, but that process of creating a pivot table can be automated. Turn on the macro recorder and establish a pivot table. Code similar to the following will be created. You can edit it to perform similar tasks in the future.

    'Create the Pivot Table
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R40C27").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
 
    'Set up columns
    With ActiveSheet.PivotTables(1).PivotFields("1/1/2006")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(1).PivotFields("2/1/2006")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables(1).PivotFields("3/1/2006")
        .Orientation = xlColumnField
        .Position = 3
    End With
    With ActiveSheet.PivotTables(1).PivotFields("4/1/2006")
        .Orientation = xlColumnField
        .Position = 4
    End With
 
    'Hide the field list
    ActiveWorkbook.ShowPivotTableFieldList = False

 

[seena]

Wonderful, this is a work around but it works and will same time for future runs. Many Thanks.