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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
I am basically trying to pivit some data and then copy the pivited data and paste it to another sheet. Maybe this helps.
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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.
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
I am assuming this can't be done then, or my post is unclear. Let me know. Thanks.
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,914
Messages
5,575,011
Members
412,634
Latest member
Sumanmathew
Top