VBA: help cleaning up code that will not work :)

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
This is some code that I recorded and then tried to modify so that I could run it as a module. This errors out right within the first With statement. Dores anyone see what is wrong with this code?

Code:
Sub Test()

    With ActiveWorkbook.Sheets("Raw Data")
        .PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Raw Data'!R1C1:R2048C36").CreatePivotTable TableDestination:= _
        "[Reports.xls]Results!R6C2", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion10
    End With
    With ActiveWorkbook.Sheets("Results")
        .ShowPivotTableFieldList = True
        .PivotTables("PivotTable1").PivotFields ("Product Type")
        .Orientation = xlRowField
        .Position = 1
        .PivotTables("PivotTable1").AddDataField Sheets("results").PivotTables( _
        "PivotTable1").PivotFields("EUHD Handle Time"), "Sum of EUHD Handle Time", _
        xlSum
        .[C8].Sort Key1:="R8C3", Order1:=xlDescending, Type:=xlSortValues, _
        OrderCustom:=1, Orientation:=xlTopToBottom
'    .Range("B8:C73").Select
'    Selection.Copy
        .Range("B8:C73").Copy
    End With
    With Sheets("Filtered Data")
        .[B11].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
End Sub

Thanks for any help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I am basically trying to pivit some data and then copy the pivited data and paste it to another sheet. Maybe this helps.
 
Upvote 0
This is the code before I modified it.

It's suppose to get it's data from Sheet 'Raw Data' and create the table on Sheet 'Results'.. Then copy the values of the table and then paste only the values to Sheet 'Filtered Data!B11' with a paste special.

I thought this would be easy but when I record myself doing this manually, the resulting code fails. I need this to work as a standard module.

Code:
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/28/2005 by David B. Mathis
'

'
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Raw Data'!R1C1:R2048C36").CreatePivotTable TableDestination:= _
        "[Reports.xls]Results!R1C1", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("EUHD Handle Time"), "Sum of EUHD Handle Time", _
        xlSum
    Range("B3").Select
    Selection.Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
        OrderCustom:=1, Orientation:=xlTopToBottom
    Range("A3:B68").Select
    Selection.Copy
    Sheets("Filtered Data").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I appreciate any help.
 
Upvote 0
I am assuming this can't be done then, or my post is unclear. Let me know. Thanks.
 
Upvote 0
I fixed it.

Code:
Option Explicit

Sub handleTime()

Dim hidden As Worksheet
Dim PT As PivotTable
Set hidden = Sheets("Hidden")

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Raw Data'!R1C1:R2048C36").CreatePivotTable TableDestination:= _
        "[Reports.xls]Hidden!R1C1", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion10
    With Sheets("Hidden").PivotTables("PivotTable1").PivotFields("Product Type")
        .Orientation = xlRowField
        .Position = 1
    End With
        hidden.PivotTables("PivotTable1").AddDataField hidden.PivotTables( _
        "PivotTable1").PivotFields("EUHD Handle Time"), "Sum of EUHD Handle Time", _
        xlSum
    With Sheets("Hidden")
        .[B3].Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
        OrderCustom:=1, Orientation:=xlTopToBottom
        .[A3:B68].Copy
    End With
    With Sheets("Filtered Data")
        .[B11].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    
' Delete any prior pivot tables

    For Each PT In hidden.PivotTables
    PT.TableRange2.Clear
    Next PT

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top