Results 1 to 3 of 3

Pivot Table Script Problem

Threaded View

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Pivot Table Script Problem

    Hi,
    I have used this script in the past with several worksheets and had no problems. I recently made some major changes to the worksheet I am now trying to run this on and now I have problems.

    I first thought that all the code in the new sheet was causing a conflict so I thought I would try to remove all the code then try this script. However no luck...

    I keep getting an error when trying to create the pivot table
    Run Time Error  '1004'
    The PivotTable field name is not valid. 
    To create a PivotTable report,you must 
    use data that is organized as a list with 
    labeled columns. if you are changing the 
    name of a PivotTable field, you much type
     a new name for the field.
    I have attached the sheet I am running this script on to help whomever can help figure out what is wrong.

    Thank You, Mike

    Sub LANEW() 'New Production Sheets
     'On Error Resume Next
            'Call RemoveAllMacros
            'Call RemoveCodeLANEW
        
        ActiveSheet.unProtect "tomcat"
        ActiveSheet.Shapes("CommandButton3").Delete
        ActiveSheet.Shapes("CommandButton4").Delete
        ActiveSheet.Shapes("CommandButton5").Delete
        
        
        Rows("1:3").Delete Shift:=xlUp
        Columns("B:B").Delete Shift:=xlToLeft
        Columns("D:D").Delete Shift:=xlToLeft
        Columns("G:I").Delete Shift:=xlToLeft
        
        
            Application.ScreenUpdating = False
            [A:A].AutoFilter Field:=1, Criteria1:="="
            [A2:A5000].SpecialCells(xlVisible).EntireRow.Delete
                If [A1] = "" Then [1:1].Delete
                ActiveSheet.AutoFilterMode = False
                
             
            [B:B].AutoFilter Field:=1, Criteria1:="="
            [B2:B5000].SpecialCells(xlVisible).EntireRow.Delete
                If [B1] = "" Then [1:1].Delete
                ActiveSheet.AutoFilterMode = False
                Application.ScreenUpdating = True
                
                Columns("A:A").Select
                Selection.CheckSpelling SpellLang:=1033
                
                Range("b1").Value = "Order #"
                Range("a1").Value = "Name"
                
    Call PivotPROLANEW
    
        
        
    End Sub
    Sub PivotPROLANEW()
    
    
        Dim ws As Worksheet, strSource As String
        Dim PT As PivotTable, pc As PivotCache, pf As PivotField
       
        For Each ws In ActiveWorkbook.Worksheets
            ActiveSheet.unProtect "tomcat"
            
             'ActiveWorkbook.ShowPivotTableFieldList = False
            ' Application.CommandBars("PivotTable").Visible = False
             'Application.DisplayAlerts = False
             'Application.ScreenUpdating = False
    
    
            strSource = ws.Name & "!R1C1:R2000C8"
            ActiveWorkbook.Worksheets.Add      'new worksheet becomes the active sheet
            ActiveSheet.Name = "Info"
            
            Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, strSource)
            Set PT = pc.CreatePivotTable(Range("A1"))   'pivot table at A1 on new sheet
            
            Set pf = PT.PivotFields("Order #")
    
            PT.AddDataField pf, "Count of Order Number", xlCount
            PT.AddFields RowFields:="Name"
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
                
        Next ws
            DATA = "Data"
            
            Worksheets.Add().Name = DATA
            Sheets("Info").Select
    Attached Files Attached Files
    Last edited by realniceguy5000; 05-20-2009 at 10:32 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1