Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot table?

**kenadams378** · 05-10-2012, 05:40 AM

I have a spreadsheet of data that i need to create a pivot table from.

I have attached the data i use and what the end result looks like also with the macro that runs to create it, my question is how can i speed this macro up, i know there are some functions life trim, & PTC & PT that will help but unsure of what i can do to complete this;

The macro currently is;

Please Login or Register  to view this content.

Any help is greatly appreciated

**kenadams378** · 05-10-2012, 08:24 AM

I have the attachment if required

**kenadams378** · 05-14-2012, 03:43 AM

Is there anyone that can help at all?

**JosephP** · 05-14-2012, 04:24 AM

a sample workbook would sure help but here's my best guess

Please Login or Register  to view this content.

**kenadams378** · 05-14-2012, 05:23 AM

There is an error around this line;

Set pt = pt.CreatePivotTable(TableDestination:=wsPivot.Range("A1"), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10)

Error is a Runtime 91 - Obkect Variable or with block variable not set

**JosephP** · 05-14-2012, 05:28 AM

that ain't the code I posted-it's pc.createpivottable and not pt.createpivottable

**kenadams378** · 05-14-2012, 05:49 AM

Set pc = pc.CreatePivotTable(TableDestination:=wsSheet1.Range("A3"), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10)

This line now brings the error;

Runtime Error 424

Object Required

**JosephP** · 05-14-2012, 06:02 AM

do you have an aversion to copy/paste?

Please Login or Register  to view this content.

note that second word is PT and third is PC

**kenadams378** · 05-14-2012, 06:14 AM

Set pt = pc.CreatePivotTable(TableDestination:=Sheet1.Range("A3"), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10)

Still same error.

**JosephP** · 05-14-2012, 06:19 AM

what's the full code you are using and do you actually have a sheet whose codename (not tab name) is Sheet1?

**kenadams378** · 05-14-2012, 06:22 AM

Full Code;

Please Login or Register  to view this content.

Data is currently over 40k lines

No coded sheet names, just the tab name for the data of 'data'

**JosephP** · 05-14-2012, 06:25 AM

this line:

Please Login or Register  to view this content.

should be (as posted)

Please Login or Register  to view this content.

**kenadams378** · 05-14-2012, 06:30 AM

Thank you for all your help, it now says - Object Variable or With Block variable not set for this line;

.ManualUpdate = True

**JosephP** · 05-14-2012, 06:35 AM

I don't see how that is possible with the code as posted since pt was set in the previous line. (I assume you reran the code)

**kenadams378** · 05-14-2012, 06:41 AM

Yep re ran the code 2 or 3 times.

**JosephP** · 05-14-2012, 06:56 AM

I just tested it to make sure and it runs fine for me so just to be sure can you copy and paste the current version of the code that you are running?

**kenadams378** · 05-14-2012, 07:53 AM

Please Login or Register  to view this content.

**JosephP** · 05-14-2012, 08:00 AM

ah - typo this line:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

note change from R1A1 to R1C1 in the SourceData argument. strange you didn't get an error earlier in the code though.

**kenadams378** · 05-14-2012, 08:06 AM

Thanks for hihglihgting that.

It's still not happy about this line;

Please Login or Register  to view this content.

I'm using Excel 2010, would it be an issue with differences in software?

**JosephP** · 05-14-2012, 08:10 AM

no - that's what I tested on. sample workbook would be very useful now I reckon.

**kenadams378** · 05-14-2012, 08:18 AM

Sample attached, had to trim the data down to 5 lines as the spreadsheet is normal around 120mbSample.xls

**JosephP** · 05-14-2012, 08:25 AM

works for me once I change the sheet name in the SourceData part from S29 to Data as in your sample workbook, and alter the DataPivotField part to:

Please Login or Register  to view this content.

**kenadams378** · 05-14-2012, 08:30 AM

Can you send me the spreadsheet you have used with a completed version of the macro on it, i can then use this as the template to run the macro?

I've still got issues with this line, that it doesn't like;

Please Login or Register  to view this content.

**JosephP** · 05-14-2012, 08:43 AM

see attached version of your file.

**kenadams378** · 05-14-2012, 09:52 AM

Many thanks for this, it works within the current spreadsheet i think the issue i had was trying to run the macro from a central summary sheet.

The macro will need to be used monthly with different monthly data, do you know how i could use this macro as a central stand alone piece?

Many thanks for all your help!

**JosephP** · 05-14-2012, 10:01 AM

it depends on what you mean by different data.
you can save the macro in a personal macro workbook or an add-in and run it from there.

**kenadams378** · 05-14-2012, 10:05 AM

The data will essentially be the same but for a different period and will be in a different excel sheet.

Ideally a central document would have the macro in it to prompt for the file required to create the pivot table?

I thought the coding at the beginning of the macro would do this?

**JosephP** · 05-14-2012, 10:18 AM

the code at the start does prompt for a filename but you don't then use that anywhere in the code - you open a file that is specified in the code.

**kenadams378** · 05-14-2012, 10:26 AM

Is there a way to change it, so it opens the file that it prompts the user for?

**JosephP** · 05-14-2012, 10:28 AM

yeah - use

Please Login or Register  to view this content.

instead of the fixed path you are using now.

**kenadams378** · 05-14-2012, 10:33 AM

How would this appear in the full code as there are numerous mentions of that coding;

Please Login or Register  to view this content.

**JosephP** · 05-14-2012, 10:44 AM

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

btw, I suggest you learn to use code tags before a moderator comes along.

**kenadams378** · 05-14-2012, 10:54 AM

Ah apologies, i tried that earlier using

code[]
&code[]/

Does this not work?

**kenadams378** · 05-14-2012, 10:55 AM

Thanks for this!

Using this change i get an error with the following line;

Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range("A3"), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10)

**JosephP** · 05-14-2012, 10:59 AM

what error? is the sheet name correct?

tags are actually
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

**kenadams378** · 05-15-2012, 03:51 AM

The error i now have is with the current line

Please Login or Register  to view this content.

The error is - Unable to set the number format property of the pivot field class

**JosephP** · 05-15-2012, 04:13 AM

I'd just remove that line.

**kenadams378** · 05-15-2012, 04:24 AM

Spot on. That has all worked now.

I was thinking last night, how could i add in the steps that open the source data then copy and paste it to another spreadsheet?

So once i open the newFN, copy all the data in the sheet and paste it to another location?

**JosephP** · 05-15-2012, 04:35 AM

that seems like a different question to me

**kenadams378** · 05-15-2012, 04:46 AM

Apologies, i have started a new thread..

Much apprecaite your help

Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot table?

LinkBack

Thread Tools

Rate This Thread

Display

Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot table?

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Re: Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot ta

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions