Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 8th, 2005, 08:21 AM
Authorized User
 
Join Date: Sep 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default pivot table not relative


hi there guys ang gals

i have a problem with a pivot table i have recorded the following code with the macro recorder and it works fine, but it only works with the file "et-0171" how can i make it work for any file and any range(depth of rows)??

thanks for your time.



Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'et-0171'!R1C11:R12004C12").CreatePivotTable TableDestination:= _
        "'[et-0171.xls]et-0171'!R1C13", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
       ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With

 
Old September 8th, 2005, 09:56 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Thelos

Dim myname As String
myname = ActiveWorkbook.Name
Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'et-0171'!R1C11:R" & Range("L65000").End(xlUp).Row & "C12").CreatePivotTable TableDestination:= _
        "'[" & myname & ".xls]et-0171'!R1C13", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
       ActiveSheet.PivotTables("PivotTable2").AddDataFiel d ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With


Cheers

Matt

 
Old September 8th, 2005, 10:19 AM
Authorized User
 
Join Date: Sep 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi matt

do i need to replace all references to et-0171 and replace them with myname??
i tried the amended code but it is throwing up a run time error 5

thanks for your time man!!

 
Old September 8th, 2005, 11:23 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Thelos

It depends if the sheet name is consistantly et-0171. If it is then you don't have to do anything, if not, then you must replace the code to something like the following...

Dim myWBook As String
Dim myWSheet As String
myWBook = ActiveWorkbook.Name
myWSheet = ActiveSheet.Name
Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
        "'" & myWSheet & "'!R1C11:R" & Range("L65000").End(xlUp).Row & "C12").CreatePivotTable TableDestination:= _
        "'[" & myWBook & ".xls]" & myWSheet & "'!R1C13", TableName:="PivotTable2"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields ("concat")
        .Orientation = xlRowField
        .Position = 1

    End With
     ActiveSheet.PivotTables("PivotTable2").AddDa taField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
End With
 
Old September 9th, 2005, 02:44 AM
Authorized User
 
Join Date: Sep 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi there mjppaba,

i should have said it is to be used for a number of differently named sheets, but all the information i want is always in columns k&l.
i am relativley new to the vb as you might of been able to tell from the amount of posts i have thanks for the time mjppaba.






Similar Threads
Thread Thread Starter Forum Replies Last Post
DefaultVersion in Pivot Table Amit Mohanty Excel VBA 1 August 1st, 2006 07:40 PM
Pivot Table vbsolo Excel VBA 3 November 23rd, 2005 01:28 AM
pivot table without total thelos Excel VBA 1 September 23rd, 2005 11:40 AM
Pivot Table mikeparams SQL Server 2000 1 February 9th, 2005 10:10 AM
Pivot Table ramdasu Excel VBA 0 October 23rd, 2003 03:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.