run-time error 1004

[john]

I am trying to create a macro that will select a certain
group of people out of a list of 30 in a pivot table.
Every time I run the macro, it ends up in a run time error
1004. and in the debug process, its always the first line
that sets the pivottable name.visible to true.
anythoughts on how to get around this. it worked for me
before, but then stopped when I re-created the
pivottable...

this happens even if I redo the macro....Thanks a million

 

[Tom Ogilvy]

Debra Dalgleish says you have to set sort to manual to make an item visible.
(it is not clear this is what you are doing, but I will assume so).
Perhaps your table was already set to manual and has been reset. See here
code.

Debra Dalgleish

http://groups.google.com/[email protected]&oe=UTF-8&output=gplain

Message-ID: <[email protected]>
Date: Sat, 16 Aug 2003 07:32:01 -0400
From: Debra Dalgleish <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.0.1)
Gecko/20020823 Netscape/7.0
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: changing sort for pivot table fields
References: <[email protected]>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: hse-sudbury-ppp329931.sympatico.ca 64.231.156.34
Lines: 1




The following code will capture and reset the sort order for each field:
'========================================
Sub PivotShowItemResetSort()
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
intASO = pf.AutoSortOrder
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
pf.AutoSort intASO, pf.SourceName
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'========================================