[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBA Please

Posted on 2013-01-30
4
Medium Priority
?
246 Views
Last Modified: 2013-02-01
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
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38837782
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
 

Author Comment

by:Rayne
ID: 38838322
Perfect Brian,

You are awesome :)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38839634
Thanks, Rayne. Glad to help!
0
 

Author Comment

by:Rayne
ID: 38842991
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question