Pivot table and it's macro

I am creating a table and from the table a pivot table using a macro.  When I 
run the macro the table gets created without a problem but when it goes to 
create the pivot table I get an error and the debugger highlights this 
section:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table8", Version:=xlPivotTableVersion12).CreatePivotTable 
TableDestination _
        :="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _
        xlPivotTableVersion12

Can anyone see where the problem is happening?
0
Utf
3/23/2010 8:03:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
613 Views

Similar Articles

[PageSpeed] 47

My guess is that a pivot table already exists at that location or a pivot 
table already exists with that name "PivotTable1".  Change the location and 
name of the pivot table being created or delete the old pivot table before 
running this macro.

0
Utf
3/23/2010 9:31:02 PM
Reply:

Similar Artilces:

manually update field
currently we have a contract amount field for a table - many Many queries,forms, reports are presently using this table- we are manually recalculating this contract amount - I have created a query to update-recalculate the contract amount (based new workorders)- BUT!!!! - now I don't know how to handle past data - using this contract amount from the table - always query Past - present date - what am i missing- on how to handle??? Also don't know of a good way to find out all of the queries - reports, forms using this contract amount field - tried object dependencies sa...

Pivot Chart Resets Changes
I'm using a pivot chart for the first time and having some difficulty when the data refreshes. The original chart was an area chart but I changed the second series to be a column chart on the second axis. However, when the pivot table refreshes it changes back to an area chart for both series. What is up? I've written some VBA code to change it back to the way I want but I'd like to know if this is typical of pivot charts or if there is a way I can avoid it. Thanks. - John This is a known problem. There's some info in the following MSKB article, which suggests creating ...

Updating 2003 Excel Pivot Chart in 2007
When I go to Chart Toolbar, Design tab, Data/Select Data The Data Range is grayed out I can not click on the Collapse Dialog Button Any help would be greatly appreciated - it used to be so easy and uncomplicated! Thanks CA HR User ...

Macros #3
Dear Sir How can I make a macro which will delete all the Blank rows from a data before pasting it in Excel Sub Test() Dim iLastRow As Long Dim i As Long Dim rng As Range iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow If Application.CountA(Rows(i)) = 0 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Delete End If End Sub -- HTH RP (remove nothere from the ...

How to maintain consistency with MFC-Macros
Dear all, I have ever so often shot myself in the foot with MFC macros like BEGIN_MESSAGE_MAP IMPLEMENT_DYNCREATE and more. The point is that all of these macros take your class and parent-class as parameters. If during development your class hierarchy changes and you forget to update those macros, things can go wrong in just about any way. Some cruel and some very subtle. Now, what I am about is the subtle errors that arise when changing things like your class hierarchy, because the errors that arise might be hard to notice and even harder to find (it has already taken me days...

creating forms with multiple tables
I have Access 2002, I created two tables, one that has all our customer information and the other will put in information pertaining to services rendered on a customers' pc. My question is, I created a form combining these two tables. I did it all as one form instead of using a sub form. When I go to the form to start entering information it will allow me to add the information for the customer but not the information for the computer. The table with the customer information has all the information in it, the table that will have the computer information gets added as the forms ...

Sort Macro #2
I recorded this macro to sort a list according to a custom list. The problem I have is that it does not sort on another users Excel because they don't have the same customs list. Is there a way round this problem? Sub Macro1() Columns("N:N").Select Selection.Sort Key1:=Range("N1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=12, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Hi! Application.AddCustomList ListArray:=Range("A1:A10") will add a custo list to a workbook using a range y...

Macro Quandry
I am attempting to run a multi-step If-Then macro. Please see below, it never gets past the first If statement line. Sub Forecast() ' ' Forecast Macro ' Macro recorded 07/27/2005 by D. Baron ' ' If B5 = G1 Then Application.Run "'SSA Business Review Tool_rev4.xls'!Oct_Forecast" ElseIf C5 = G1 Then Application.Run "'SSA Business Review Tool_rev4.xls'!Nov_Forecast" ElseIf D5 = G1 Then Application.Run "'SSA Business Review Tool_rev4.xls'!Dec_Forecast" ElseIf E5 = G1 Then Ap...

import data using macro
I'm trying to import data with exel. I import ten sets of data that are written in xls format. Here is my method for importing. In the data tab I press import external data and then press next and then add a space. I do the same process ten times and was wondering if I could streamline this process with a macro. Would it be possible to import one set and then teach the macro to import the next 9 data files in succession. The name of the files will change every time, but they always are in order (i.e. 718cr1, 718cr2, 718cr3, etc.). The 718 represents today's date, which is...

Prevent combo box from updating a table
My application includes a Combo Box which gets it's values from a query, when I close the form it inserts the value which is in the combo box into the table from which that data came from. How do I prevent the insert? On Thu, 11 Mar 2010 20:33:28 GMT, "avtuvy" <u58699@uwe> wrote: >My application includes a Combo Box which gets it's values from a query, when >I close the form it inserts the value which is in the combo box into the >table from which that data came from. How do I prevent the insert? If the purpose of the combo box is just to find a r...

MAcros big Q!
i got a Q, i know pretty much about using the formulas in excel, but still too rookie using macros, so i got a Q about it. i made a macro, the result is a graphic char, when i record the macro i folow all the steps to create the char, and specify the chart has to be in the same SHEET, i mean not in a new one, and finally i get my chart and stop the macro. when i try to run the macro in a new doc, it works, but the chart appears in a new sheet, anyone knows about that? ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/fo...

I don't have a macro but it says I does
Every time I open my daily active file in excel it says there are macros when there aren't (see my active file) How do I find out if there are macros in a file and how do I delete them? Hi Terry, See Debra Dalgleish's suggestions at: http://www.contextures.com/xlfaqMac.html - NoMacros --- Regards, Norman "TerryTutor" <TerryTutor@discussions.microsoft.com> wrote in message news:2E422984-6C1B-4E6A-92B2-A97CE804BDEC@microsoft.com... > Every time I open my daily active file in excel it says there are macros > when there aren't (see my active...

Define Name use in Macros
I am not sure how or if you can use the Define Name function in a worksheet to assign a value in an Macro. I am using the command below to assign a value in the worksheet in the macro. However the user might make changes to the worksheet that makes this method invalid. If I use the Define Name function in excel and create a Name can I then us it to assign a value? Set Total_Assets = Sheets("Balance Sheet").Range("H55") You should definitely assign a name (Ctrl-F3) to this cell and all ranges your macros used, in case you or your users insert rows, etc. Set Total_...

Pivot table to calculate variance showing budget and actual.
Hi, I know this is rather simple, but I just could not get it right. I have this data: Expenese Type Amt Scenario Staff cost 200 Budget Admin cost 75 Budget Office rent 5000 Budget Staff cost 100 Actual Admin cost 45 Actual Office rent 300 Actual I am able to get the difference using the pivot table "Difference from", but it only showed that single column. I would like my pivot table to show: Expenese Type Budget Actual Variance Staff cost 200 100 1...

Opening a file with a Macro
To open files with a macro, I am currently using: Workbooks.Open Filename:= "C:\file_path.xls" How can I use a variable as part of the file path? In other words, before I try to open the file in my VBA code I will set a variable (let's call it date_extension). Then I want to open a file that I know will exist with a file path whose final characters before the .xls will be the date_extension. How can I get the file path to be recognized with the variable? Thanks for your help (P.S. I am a total novice, so I may not be familiar with commands or procedures that are fair...

How do i start Macros using IF statements?
How can i use an IF statement to start a macro using data validation to get a list in a cell if the previous cell equals a specific word. Eg. IF C1 = "Bedroom" then it will trigger the macro to use data validation in the next cell to display a list of diffent number of bedrooms which the user can then choose. You need a worksheet change event macro to monitor C1 and respond accordingly. See: http://www.mvps.org/dmcritchie/excel/event.htm for complete details. Good Luck -- Gary's Student "xXx Katie xXx" wrote: > How can i use an IF statement to start a ma...

Check box and make table query
I am trying to write a very basic make table query pulling some of the data from a form. the form has a check box, [MO] and a default value of -1 (this is to defaul all records as checked) If I run a datasheet veiw of the query, the table populates with the "-1" when I run the make table the field shows ?? and a data type of bianary. The query is pulling from the form as MOR:[forms]![Frm_RunNew]![MO] If I uncheck the box it show zero on view and null when run. any suggestions? I added Cbool to my query and all is fine now.. thanks to anyone who may have taken t...

excel to html looses button and macro, why?
When saving excel to html using mso 2003 I loose my execute button that runs my macro. I have tried saving sheet, workbook, saving as mhtml. Is there no way to get the macro module exported into html? Thanks, Kelly. ...

Edit a table while in form view
I have a form that has multiple tabs and subforms. On one of the tabs is a subform with a recordsource that is a query. It shows data from a table in datasheet mode. In that table I have a field "Selected" which is a Yes/No field (checkbox). In form view, I want to be able to check the checkbox in the table in the subform. I don't have that field locked, what other settings should I check? Thanks, Akilah Can you update fields in the query if you open the query by itself? You need to determine whether it is the form that is locking the fields or the query itself. ...

Ho to Delete "Ghost" Pivot Tables
Hi, I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effecei...

pivot table cannot group that selection
I cannot get a column of data to group using a Pivot table. I have done all the suggestions I've ever know to do in my data which are: - i removed ALL blank entries - i did the "text to columns" trick to convert all data to text, then applied appropriate formatting for the data type. - i copied all data and pasted special to ensure only the values were entered. none of this works and i don't know what else to try. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

protect macro
how to protect a macro by giving a password -- Message posted from http://www.ExcelForum.com You can protect your project by going into the VBE (Alt-F11 is one way). Selecting your project Tools|VBAProject Properties|Protection tab. That said, there are password crackers out there. But it keeps most out of your code. "nowfal <" wrote: > > how to protect a macro by giving a password? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com thanks Mr.Dav -- Message posted from http://www.ExcelForum.com ...

How do I create a combination chart and table with different data.
I am using Windows XP, and Microsoft Excel 2003. I have been asked to create scorecards with charts displaying rates graphically, and an attached table underneath with numerator and denominator data. The combination graphs in the custom charts try to graph and display all the data. Can I create a combination chart with the rates graphically displayed and the underlying numerator/denominator data presented in a linked table underneath the chart? Or do I need to create the graph and table separately? You would do better to make a separate chart and table, because chart data tables ...

Pivot Tables
Hello, I run into a situation i dont know how to solve. The source table looks like this; WKSNAME,OS,APPLICATION WKS0001,XP,Excel WKS0001,XP,Word WKS0001,XP,Powerpoint WKS0002,Vista,Word WKS0002,Vista,Excel WKS0003,XP,Outlook I'd like to count the number of XP workstations by using a pivot table. like this: OS "count of WKSNAME" XP 2 Vista 1 The problem i run into is that it counts the nummer of rows (resulting in 4 XP workstations) where i want to count the Unique rows. Filtering the source data doenst give the desired result, i cant modify the source data either. ...

how to make a table with 10 million rows in one column?
how to make a table with 10 million rows in one column? Num 1 2 3 .. .. .. 10,000,000 thanks, pemt WHY! -- Build a little, test a little. "pemt" wrote: > how to make a table with 10 million rows in one column? > > Num > 1 > 2 > 3 > . > . > . > 10,000,000 > > thanks, > > pemt On Mon, 22 Mar 2010 09:44:01 -0700, pemt <pemt@discussions.microsoft.com> wrote: >how to make a table with 10 million rows in one column? > >Num >1 >2 >3 >. >. >. >10,000,000 ...