Creating a dyanmic pivot table

I am in the process of creating a macro which will be able to select a
table and create a pivot table based on that data.  The data changes on
a weekly basis. ie the number of rows.  How do i create a table based
on this  The code i have seem to only accept "hard coded" data ranges.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Duplicates Removed'!R1C1:R11356").CreatePivotTable
TableDestination:="", _
       TableName:="PivotTable3"
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
   ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
        "Company Code Name", ColumnFields:="Status of Response"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Status of
Response"). _
        Orientation = xlDataField

I have seen something on Dynamic ranges but not sure how or if this
will work.  Any ideas?

0
11/1/2006 2:51:03 PM
excel 39879 articles. 2 followers. Follow

3 Replies
365 Views

Similar Articles

[PageSpeed] 23

Hi,

If you pop this at the start it will find the last poulated row...(just
change the "A1".select to the a colum which will be populated all the way
down.

Dim r_lastrow As Integer
Dim current_cell As String

current_cell = ActiveCell.Address
Range("A1").Select

Selection.End(xlDown).Select
r_lastrow = ActiveCell.Row
Range(current_cell).Select

then change your pivot reference to: (and amend C2 to the correct column
reference)

SourceData:="'Duplicates Removed'!R1C1:R" & r_lastrow & "C2"

one thing to note, in your origional example, you missed the Cx from the end
of range statememnt (R1C1:R11356 shoudl be something like R1C1:R11356C2)

HTH

Simon

Mistry wrote:
>I am in the process of creating a macro which will be able to select a
>table and create a pivot table based on that data.  The data changes on
>a weekly basis. ie the number of rows.  How do i create a table based
>on this  The code i have seem to only accept "hard coded" data ranges.
>
>ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
>        "'Duplicates Removed'!R1C1:R11356").CreatePivotTable
>TableDestination:="", _
>       TableName:="PivotTable3"
>   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
>1)
>   ActiveSheet.Cells(3, 1).Select
>    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
>    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
>        "Company Code Name", ColumnFields:="Status of Response"
>    ActiveSheet.PivotTables("PivotTable3").PivotFields("Status of
>Response"). _
>        Orientation = xlDataField
>
>I have seen something on Dynamic ranges but not sure how or if this
>will work.  Any ideas?

-- 
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot  ]com

Message posted via http://www.officekb.com

0
smw226
11/1/2006 3:36:12 PM
Hi

On your sheet Duplicates Removed, create a named range
Insert>Name>Define>Name>  Myrange
>Refers to   =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Then change in your code
SourceData="'Duplicates Removed'!Myrange"
-- 
Regards

Roger Govier


"Mistry" <miteshsmistry@gmail.com> wrote in message 
news:1162392662.884297.91270@h48g2000cwc.googlegroups.com...
>I am in the process of creating a macro which will be able to select a
> table and create a pivot table based on that data.  The data changes 
> on
> a weekly basis. ie the number of rows.  How do i create a table based
> on this  The code i have seem to only accept "hard coded" data ranges.
>
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
>        "'Duplicates Removed'!R1C1:R11356").CreatePivotTable
> TableDestination:="", _
>       TableName:="PivotTable3"
>   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
> 1)
>   ActiveSheet.Cells(3, 1).Select
>    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
>    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
>        "Company Code Name", ColumnFields:="Status of Response"
>    ActiveSheet.PivotTables("PivotTable3").PivotFields("Status of
> Response"). _
>        Orientation = xlDataField
>
> I have seen something on Dynamic ranges but not sure how or if this
> will work.  Any ideas?
> 


0
roger5293 (1125)
11/1/2006 4:41:26 PM
Have you considered using an existing file as a template.  Get all your 
Pivot tables in place, use a dynamic range to get the data and then just 
paste the new data each time over the old and refresh the tables?

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

------------------------------�------------------------------�----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------�------------------------------�----------------



"Mistry" <miteshsmistry@gmail.com> wrote in message 
news:1162392662.884297.91270@h48g2000cwc.googlegroups.com...
>I am in the process of creating a macro which will be able to select a
> table and create a pivot table based on that data.  The data changes on
> a weekly basis. ie the number of rows.  How do i create a table based
> on this  The code i have seem to only accept "hard coded" data ranges.
>
> ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
>        "'Duplicates Removed'!R1C1:R11356").CreatePivotTable
> TableDestination:="", _
>       TableName:="PivotTable3"
>   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
> 1)
>   ActiveSheet.Cells(3, 1).Select
>    ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
>    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:= _
>        "Company Code Name", ColumnFields:="Status of Response"
>    ActiveSheet.PivotTables("PivotTable3").PivotFields("Status of
> Response"). _
>        Orientation = xlDataField
>
> I have seen something on Dynamic ranges but not sure how or if this
> will work.  Any ideas?
> 


0
ken.wright (2489)
11/1/2006 9:47:03 PM
Reply:

Similar Artilces:

Account balance table?
Does anyone know if there is a table in Great Plains that contains the life-to-date balance for balance sheet accounts? I can obtain the debits and credits for balance sheet accounts which gives me the activity for a period, but what I really need is a simple way to pull the life-to-date balances. For example, if I'm in period 5 of 2004, I need to pull all my balance sheet account balances at that point in time. Thanks ...

Font sizes not saving (W2007 opening document originally created in W2003)
Hello, I have a 375 page document originally created in W2003 (or perhaps W2000). It was creeated with a Normal style of TNR 10 pt. Everytime I open it I have to change the font size from 11 pt back to 10 pt. When I modify, the radio button for "Only in this document" is checked but the changes don't keep. I really don't want to change my Normal template for new documents, but I'd like the font size to be retained. Am I missing something simple or do I have to mess with templates? Thanks, CJ This should not be happening unless you have "Automat...

Creating a rule with people not in contacs
I haven't been able to find a way to create a rule where I forward the incoming email to someone not in the contact list. I want to immediately forward certain spam to the ISP's abuse email address (aol, yahoo, etc). I see no reason to have to add those addresses to my contact list, yet I see no way to do it without adding. When I get to the dialog for forwarding it tells me to type an address or find it in the list. However, if I type an address not on the list it won't let me add that name. Using help I've found out how to build a rule on an inbound email address not in my ...

Table / Column descriptions
Is there ANYWHERE that you can find what the column names in each table represent ? Such as there is a column in the SOP10200 table that is PURCHSTAT. Some of them are a 1 and some are a 2. What does this represent ? Where can we find out a detailed list of what these columns are in the tables ? Why does MS not provide this ? Is it a secret or is it just because they dont even know ? This is VERY frustrating in trying to troubleshoot Great Plains .... or shoud I say Great PAINS !! Accolade has been the best so far, just not as detailed as one would need for accurate tro...

Table headings in Excel
Hi, How do I get table headings to repeat at top of each page? Thanks, Allie Hello, try File - page setup - sheet then enter your range in 'rows to repeat at top' Suddes "Allie" wrote: > Hi, > > How do I get table headings to repeat at top of each page? > > Thanks, > Allie Suddes' response is correct for printing. If you're wanting them to display as you scroll down, you can split/freeze the panes. >-----Original Message----- >Hi, > >How do I get table headings to repeat at top of each page? > >Thanks, >Allie >. &...

Pivot Table Drop Down Boxes
I want to be able to limit the drop down box content to information relevant to what I have seleced on the page. Example: I select Region A at the page selection, then open the District drop down box, and all districts countrywide are shown. I only want to see Districts to choose from in Region A. Excel 2003 - Windows XP That feature isn't available in the pivot table dropdown lists. Dave S wrote: > I want to be able to limit the drop down box content to information relevant > to what I have seleced on the page. Example: I select Region A at the page > selection, then ...

Macro to delete specific data in tables
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF ...

Combining Tables
Hello, I have 5 files in excel. These files will have the same fields, but the information will change in them every week. I am then going to import the the excel files into access into my five tables that I have created. What I need is from all those 5 five tables to be combined into one big table. I want to keep all the same fields, just a combination of the five. I have to do some modifications on that on that huge file. But my main concern is how to combine all that data into one huge file. Thanks It sounds as though you are expecting Access to be Excel ("same...

Access 2003 linked tables causes read-only
Hi A weird one - I have an Access 2k mdb set with linked tables. Ive opened the set under Access 2003. If the link is to the c: drive (where the functions are) all is OK, but if I put the mdb with the tables in on a net drive (eg X:\.....) the tables become read only and I can find how to stop this.. Any help anyone, please???!!?? thanks Jim McDonald What permissions do you have on the folder where the mdb has been placed? You must have Change permissions (Read, Write, eXecute and Delete) on the folder, not just the file. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele ...

New To Pivot Table
I have the following sales data: dept, category, description, Qty sold, sold price, total sale, date sold, cost,profit,prfit margin. the following is an example: beer 12 pks,miller lite, 2, 7.99, 15.98, 7/1/05, 13.98, 2, 14% The 12 pks represents the ctegory, miller lite represents the description, and soforth. I have about 15 different departments, I would like to setup a pivot table to track and compare the data per week, month, quarters and year, for example: total sales, profit, cost, profit margin for the first week on july for each department compared with the same da...

Filtered Records as source for appending records to a table
I have an inventory database with a form to create multiple items with the same product information at once, it goes to a second form where the individual serial numbers can be entered. The second form shows the filtered recordset of items just created. I want to be able to press a button on the second form and have a transaction created in my transactiontbl using each of the itemids in the filtered recordset. The new transaction records should be as follows... TransactionTbl TransactionID -> Autonumber Transdate -> Date () ItemID -> Number (foreign Key itemid fr...

after native mode switch can't create new mailboxes
We recently switched to Exchange 2003 native mode. Email is working fine in all sites, however when we tried to create our first user post-switch we've found that the mailbox never gets created. Same procedures as before the switch, we run AD Users on the local Exchange server, create the account, specify the server, mailbox store it's part of.... The account gets created and replicated throughout the domain environment, but the email address never gets generated for the user, and the mailbox never gets created on the destination server. We followed all the guidelines for switching ...

Use the same cell from many worksheets to create a chart?
I have a new worksheet for each day, labeled with the date. I want to pull data from the same cell (like "P-3") in each worksheet and put it into a chart. Example: On ten worksheets, pull the data from cell P-3 (arbitrarily called "income"). Then, put that P-3 data into a chart that tracks daily income: the date itself (taken from the worksheet name) plus the data. Any idea how to do this? You need to create a summary region on a worksheet, and use this as the data source for your chart. See this page for more details: http://peltiertech.com/Excel/ChartsHowTo/Char...

No inbound email after creating a new recipient policy
Hi there, I had 2 smpt email address setup in the default policy to get the inbound email working initially. Upon researching, I understand that by default the exchange is set to mixed mode which means only default policy is the only policy that exchange applies regardless. I created a new recipient policy to query company field and applied it. I then changed the exchange to Native mode and restarted the information store. I removed the second smtp address from the default policy. Now i could not receive any emails for the secondary domain via SBS mail connector pop. It picks u...

create .tmp files when saving
Am having a problem with several of my dell machines running window xp professional and office xp pro. when users open an excel file, make a change and save it, then go back in the file, many times it says the file can be open in read only because the file is in use. it then creates a copy of the file using the .tmp as an extension. Why is this happening so frequently. any help would be appreicated. collin Hi Collin Try this first Reboot first and then Delete all the files and subfolders in your \Windows\Temp directory (Win95/98/ME), or your \Documents and Settings\<user>\loc...

Incident should be create automatically like c360 products
With any parameters or config, Incidents should be created automatically like EmailToCase c360 product. Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=7b9224b9-28b...

Sorting in Pivot Charts
I'm using Access 2003 & am trying to sort in a Pivot Chart. I have a single series & I'm trying to sort by the qty of that series. Thanks much in advance, M.C. ...

Scroll Through Pivot Table Entries
XL 2007 XP I have a pivot table chart. The chart is setup to show only one entry at at time. I have with many different entries, 197 total, in the Report Filter, however, as mentioned above I only want it to display one entry at at time. Is there a way to create a scroll bar from the Forms toolbar and use that so that it causes the chart to scroll through each entry one at a time? Thanks ...

Creating scatter plots
How do I create a scatter plot. I've done line and bar graphs but I'm not sure how to do a scatter plot. Also, how can I use data from different tabs in the same excel file to create the graph. Will I need to copy and paste all the data onto another tab? Hi, For a scatter plot you need a set of x and y values. If you only have Y values the x ones will automatically be a sequential series 1 to number_of_Y_points. Ideally you would have you X-values in a column with the Y-values in the next column to the right. You can make Excels job easier at guessing your data layout if you...

Creating help
I wonder how to create help for my project? I've found an .rtf file in the hlp folder of my VC++ 6 project that seems to be the ground for the help. Are you supposed to edit this file somehow? How do you create different pages with links between them in the help? You do it it with a great deal of difficulty in my opinion. What you need is Help Workshop (HCW.EXE) which you already have or you can download from MS with which you edit contents files and compile/test the help. You need MS Word to edit the .rtf files. VS gives you a starter file (AfxCore.rtf) which you modify. You have...

How can I delete rows from Pivot Tables in Excel 2000 as in 97
I was able to delete multiple unwanted rows from Excel 97 Pivot Tables. Now I find I can only delete rows individually by untagging them in Excel 2000 Pivot Table. This is far too time consuming... Does anyone have a faster Solution ...

Table Help
Hello Everyone, I know I have seen this problem before and that I have corrected it in the past, but for some reason I can't figure it out this time or find something similar online. The problem is simply, when I view a certain table one of its columns is completely filled in with "##########". The data is still there because when I click on it the data appears, and the data is visible in a justified form. It can't stay like this but I can't figure how I did it in the first place or how to change it back. Any help would be greatly appreciated. Thank You Ko...

How can I hide check box created via FORMS together with column?
Hey guys, how can I hide a check box created using FORMS together with column it is placed in (or in some other way). "Move and size with cell" option is not active in object positioning for check boxes created via FORMS as opposed to those created via CONTROL TOOLBOX. Thanks, Max I think you'll have better luck (pronounced easier time!) with the checkboxes from the Control toolbox toolbar. But maybe you could have a macro that hides the columns/rows and also looks at the objects to see if they should be .visible = false. And the same kind of macro to make the checkbox&#...

user Unavailable when creating an activitiy
I am using CRM 3.0. I was wondering why I keep getting an error message of My name not being available when creating a scheduled assignment and then assigning the assignment to a fellow co-worker. If someone can help it would be appreciated. thanks ...

how do I create a blank HOrizontal Document?
I am trying to make an office document for work. I want to use word just like usual, but i want it to be printed horizontally so it all fits to one page becuase of a bar graph. IDK HELP!!! On Mon, 11 Jan 2010 16:11:01 -0800, nico23 <nico23@discussions.microsoft.com> wrote: >I am trying to make an office document for work. I want to use word just >like usual, but i want it to be printed horizontally so it all fits to one >page becuase of a bar graph. IDK HELP!!! Please repost your question in a newsgroup supporting Word. There's no such thing as an "O...