Program stops (creating pivot table) when too many records

Hi all,

I have a macro which creates a Pivot Table using the records I provide and 
it works fine up to probably 40,000 records.  

When the database becomes too big, the program stops at the line of code 
which creates the Pivot Table with an error message.
1) Pressing F5 simply gives the same error message.
2) Running the same macro from start with a reduced records works well again.

How can I overcome this problem?  

Thanks in advance.
0
Utf
3/5/2010 2:57:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
940 Views

Similar Articles

[PageSpeed] 24

On Mar 5, 7:57=A0am, ch <c...@discussions.microsoft.com> wrote:
> Hi all,
>
> I have a macro which creates a Pivot Table using the records I provide an=
d
> it works fine up to probably 40,000 records. =A0
>
> When the database becomes too big, the program stops at the line of code
> which creates the Pivot Table with an error message.
> 1) Pressing F5 simply gives the same error message.
> 2) Running the same macro from start with a reduced records works well ag=
ain.
>
> How can I overcome this problem? =A0
>
> Thanks in advance.

Are the variables declared have sufficient range?
0
dk
3/5/2010 8:32:30 AM
Reply:

Similar Artilces:

Once user is created in active directory, it cannot find name in o
I have recently had to create a new user in Active directory and for some time it has been working well but today it seems that I cannot link the new user to Outlook. It keeps coming up with an error saying that "the name could not be matched in the list" I have had this problem in the past but was told to update the recipient list, which I have done but it still does not seem to replicate. I know the account exists because it is in our distribution lists but not in the global list or when trying to link to the server. I understand that it may take time to replicate but I am...

Recording Section 529 Plan
I'm establishing a section 529 plan for my granddaughter. Any suggestions of how to record in Money 2003? I record mine as tax-deferred/tax-free investment accounts (specified either during setup or in the account details). Derrick <anonymous@discussions.microsoft.com> wrote in message news:620401c3e60f$78b5bca0$a001280a@phx.gbl... > I'm establishing a section 529 plan for my > granddaughter. Any suggestions of how to record in Money > 2003? ...

find record using combo box
I want to use combo box to locate a name. After update, the combo box shows the name but the form does not move to the record of combobox. Here is my code: Private Sub cboLook_AfterUpdate() Dim rs As DAO.Recordset 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[name]= """ & Me.cboLook & """" 'Display the found record in the form. Me.Bookmark = rs.Bookmark frmFlexSubform.SetFocus Set rs = Nothing End Sub You have a field named Name? Access ...

What to do?
Hi Everybody? I have problem when I try to build offline data file from Pivot tabel which is connected to Cube on the Analysis Server. Excel return this message: No columns that microsoft excel can use were returned from this query! What to do? ...

How to create column of ranks.
In one column, I have a list of 500 or so sorted prices: A 5.30 5.30 4.50 4.49 4.49 2.00 I want to rank these prices, so in the above example, I want to create a column B that would be: 1 1 2 3 3 4 (ranking the prices in column A). How do I do this? Thanks, Nitin Batra (email: nbatra@sbcglobal.net, cell: 714-488-9584) Before we get fancy, is this something you can use?: Using your list in Cells A1:A6 B1: =RANK(A1,$A$1:$A$6) Copy that formula down. The results will be this: 5.3 1 5.3 1 4.5 3 4.49 4 4.49 4 2 6 Note the lack of a 2nd and 5th rank....Excel grants ties the same rank ...

Newly Created Function not Working #3
Thank you Frank, I used your suggestion but I still was unable to ge this to work. This is actually the method I had already used. Would i be easier to just save this as an addin and have him install the addi on his machine? Do you think it may work if we do this? Again thank yo for oyur help -- nuve ----------------------------------------------------------------------- nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1003 View this thread: http://www.excelforum.com/showthread.php?threadid=26961 I'll just chime in and give my $0.02 for what it's...

Cannot insert value null into column 'Doctynam',Table tempdb
When trying to invoice a Purchase order we are getting the error Cannot Insert the value NULL into the column 'DOCTYNAM', table 'tempdb.dbo.#PODetailed_______ Column doe Have you tried stopping and starting the SQL Server service? If doing so does not fix the problem, check to see if you have any customizations. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Taff" wrote: > When trying to invoice a Purchase o...

Decimal places in a temporary table
I am using a temporary table to perform calculations before appending the resultant invoice transaction records to my main table. The temporary table is created in code like this: strSQL = "CREATE TABLE tblInvoiceTransactionsTemp (fldInvoiceTransactionID Double,fldProductID Double, fldIFAID Double,fldQuantity Integer, fldFreeOfCharge YesNo," strSQL = strSQL & "fldEnqID Double, fldInvoiceHeaderID Double, fldAgreedPrice Currency, fldVATRate Double)" DoCmd.RunSQL strSQL This all works fine but the problem is the VAT (tax) rate - the current rate is 17.5% but wh...

Outlook 2002 will not send and recieve on created account
Outlook 2002 began acting up and it lost one of my e-mail accounts. I noticed that I was not getting any e-mail that I would usually get with the account. I checked and the account is not there. The other thing that I have noticed is that my address book and archives are listed where my other accounts are listed in the area where you would edit or add e-mail accounts. I went in and recreated the missing account and went to send and recieve and it would not bring up the account in the send and recieve box (it brought up all my other accounts). I went back and the account is still...

table of contents in excel
Does anybody know how to create a dynamic table of contents in Excel (similar to Words TOC). Or alternatively how to insert the current page number into a cell so that it can be cross-referenced. Thanks Nick Hi Nick, See David McRitchies TOC page: http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- Regards, Norman "nickH" <nickH@discussions.microsoft.com> wrote in message news:28FEEDC1-6091-4FC4-BD5C-5CE2A6116A5E@microsoft.com... > Does anybody know how to create a dynamic table of contents in Excel > (similar > to Words TOC). Or alternatively ho...

Report Writer and effective dated tables
Hi, I am new to using report writer. I am trying to write a report that will just pull back the latest effecitve dated row by employee. A SQL statement that can do this would be something like: SELECT MAX(effective_date) FROM myTable WHERE effective_date <= Now(); I can not figure out how to do this in Report Writer. Any suggestions? Anyone know if this is possible? "badabing" wrote: > Hi, I am new to using report writer. I am trying to write a report that will > just pull back the latest effecitve dated row by employee. > > A SQL statement that can do thi...

One primary key per table? Access 2003 newbie
I'm under the impression that there is only one primary key per table in an Access database. I've got a class project where the professor asks to create a table of music categories. He tells me what categories are used (6 total) and he then explains that "the primary key for each category should be a short code consisting of letters. Another field will be a description of the category". I am completely lost as to what the professor is expecting. Can anyone shed some insight as to what he is expecting me to create? Please no flames about me going and asking the professor...

need help with tables so form okat- multiple equipment for 1 job
Here goes. I know this should be simple not sure why I can't see this right. Here is what I want to end up with not sure the best way to set up the tables to get what I want. and/or best way to create the form(from a query) or using form wizard pulling in tables that I need. I want to END up with a Form for all possible JOBS(cleaning jobs) with equipment needed and soaps needed for each job. There are many pieces of equipment for each job - mop, bucket, etc.(can't figure out how to get this in a form without listing with a comma) Have so far - don't thinks the tables a...

Pivot Table data source
I have created a pivot table in the same workbook as the data source but whenever I try to refresh the data I get a message saying "This command requires at least two rows of source data.............." On checking the data source some cells on only one row are selected. If I change the selection to what it should be everything works fine but the selection reverts back to the same row and cells the next time I open the workbook. How can I make Excel (2007) retain the data source selection? Hi Pete If you have given the correct range, then it should retain the values. Howe...

to shut down windows by ending a vb.net program
Is it possible to run a vb.net program and when it ends (there are many calculations) to shut down windows(for me windows 7)in the program in the last program line? And how to do this? Thanks for any response andrews wrote: > Is it possible to run a vb.net program and when it ends (there are many > calculations) to shut down windows(for me windows 7)in the program in > the last program line? > And how to do this? > Thanks for any response Windows 7 may be different, but I doubt it. http://articles.techrepublic.com.com/5100-10878_11-5030366.html From the U...

Stopping PMI
I'm using Money 2003 and have the mortgage setup with PMI as an "Other Fee." Now it is time to remove the PMI. Is the best way to remove the PMI to go into the "Change Load Terms" and delete it as an "Other Fee?" If I do that will it just affect the payments from this date on and not change the previous payments? I've done searching in this forum but I'm still confused. Timothy Yes, that's what you need to do. It will only impact future payments. -- "TimothyR" <nospam@nospam.com> wrote in message news:OGjPe.6459$WO2.1200...

Stop baby eating in CHINA
Eat dogs, cats and baby look below: http://z.about.com/d/urbanlegends/1/0/v/7/eating_babies1.jpg http://z.about.com/d/urbanlegends/1/0/w/7/eating_babies2.jpg http://888.rockin.net/propaganda-of-baby-eating/rumour.htm#true_story info@imagiam.com ...

I am looking for a network based CRM program for the Mac
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3175770517_552777 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hi, Can anyone supply me with more info on a CRM program for the Mac, that can work on a network? Unfortunately CRM4Mac doesn�t support Entourage as well. Many thanks, JC --B_3175770517_552777 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>I am loo...

How to create a column styled blog in Word
I want to use word to create content for a blog that looks like a magazine with 2 columns - how would I create a column blog ...

how can I create a directory of horse breeders with contact info?
I am currently setting up an access database of BC horse breeders with information such as, farm name, contact name, horse breed, stallion or broodmare service, address, phone/fax, email and website if applicable. My goal is to somehow turn this into a booklet much like a small phone directory, but as this is all new to me, I don't know quite how to best go about it. Publisher 2003? Catalog merge would be the way to go. Catalog merge http://office.microsoft.com/en-us/assistance/CH010504381033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://ms...

Creating a Calendar
I am using Microsoft Publisher 2000 on a machine running Windows 98 to create a twelve month calendar of events. Each month is a separate page. When I change the appearance of January, the other eleven months also change identically. This is desirable when I create the color and the location of the date numbers. Unfortunately, every time I change the size of an entry in a date box, some of the previously entered words and numbers in other months also change, and often not in the same manner. This can render some words too small to read and other words too large so they are not seen at all ...

Question about table design
Hi, ** Please find scripts below ** In my database I handle product options using three tables Attributes, Products and ProductOptions. This approach works for the most part but here's the problem I have with it: 1. Ultimately each and every item should have its own SKU so that inventory control would be possible. For example, Levi's 501 34x32 Indigo should have its own unique ProductID so that if this item is out of stock the system would show it 2. Sometimes one of the "options" is not available for a certain SKU. For example, The Levi's 501 38x...

A macro to copy & paste many rows (a range) to the next column ..
Hi, I have some data that needs to be reformatted. (see below) The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C ("Alpha"), Col D ("Sample ID"). I want to select "the Col B & Col D sorted data" to be copied and pasted to the adjacent Cols-E thru H, and so on for all the different Assays (Col B) till end of file. Can this be done through a macro? A present I am filtering on Col C and then copying and pasting manually for each Assay. e.g WELL ASSAY_ID Alpha SAMPLE_ID A01 Statin1 C 1 ...

E-mail just stopped working in GP
We currently use GP 8, on a Win2k3 TS server, along with Adobe 7.0 Pro on that box as well. For months users have been able to e-mail PDF's with out a hitch, now over the last few days the users can't and neither can the Administrator's account. Any IDEAS on why this just suddenly stopped???? Al, Are they getting an error message? -- Victoria Yudin Dynamics GP MVP "Al" <Al@discussions.microsoft.com> wrote in message news:8C0E745D-D84E-4A82-A97D-03E6F7698DA6@microsoft.com... > We currently use GP 8, on a Win2k3 TS server, along with Adobe 7.0 Pro on &g...

Table Import Assets
I've imported assets using the Table Import feature in GP. Should this create a journal for GL posting? Everytime I try to run one, it tells me, there are no records. I was able to do a depreciation run, successfully on the same assets and that did generate a GL file. Thanks, Hi, A table import to the FA tables does not create a journal entry. Did you create your own table import or did you use the import functionality that comes with the FA module? Leslie "jimr" wrote: > I've imported assets using the Table Import feature in GP. Should this create > a jo...