• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

VBA Please

There is a large Named Range (“Source”) in a excel file. Need to VBA to do this:
a.      Without removing or deleting the named range , add a table to the named range so that it gives some good format to the 30000 rows.
b.      Create a pivot table out of that named range’s range in the format  shown in ‘pivot’ sheet
Copy-of-pivotV2--VBA.xlsx
0
Rayne
Asked:
Rayne
  • 2
  • 2
1 Solution
 
redmondbCommented:
Hi, Rayne.

Please try this code...
Option Explicit

Sub Build_Name_and_Pivot()
Dim xSource    As Worksheet
Dim xPivot     As Worksheet
Dim xTableName As String

Set xSource = Sheets("source")
Application.Goto Reference:="Source"

On Error Resume Next
    Selection.ListObject.Unlist
On Error GoTo 0

xTableName = "Table1"

xSource.ListObjects.Add(xlSrcRange, Range([Source].Address), , xlYes).Name = xTableName
Range(xTableName & "[#All]").Select

Set xPivot = Sheets.Add

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=xTableName _
    , Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=xPivot.Name & "!R3C1" _
    , TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion14
    
With xPivot.PivotTables("PivotTable2")
    .AddDataField xPivot.PivotTables( _
        "PivotTable2").PivotFields("GRPAllocated$"), "Sum of GRPAllocated$", xlSum
    .AddDataField xPivot.PivotTables( _
        "PivotTable2").PivotFields("GRPAllocated$Changed"), _
        "Sum of GRPAllocated$Changed", xlSum
        
    With .PivotFields("GRP")
        .Orientation = xlRowField
        .Position = 1
    End With

    With .PivotFields("Parts")
        .Orientation = xlRowField
        .Position = 2
    End With

End With

End Sub

Open in new window

Regards,
Brian.
0
 
RayneAuthor Commented:
Perfect Brian,

You are awesome :)
0
 
redmondbCommented:
Thanks, Rayne. Glad to help!
0
 
RayneAuthor Commented:
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now