How To Loop A Recorded Macro

[rcdavis28]

Hello All,

Thank you in advance to the attention of my post. I have attached a file, where I a have a recorded macro called PTFMT (I think this is correct). How can I apply this code to other workbooks with the same identical data structure? I have tried this a couple of times and i get a run time error. I know very little about VBA as well. Thanks!

[Keebellah]

Hi, the problem you are running into is that your macro does not do everything.
If you are in the wrong sheet it will generate an error because it cannot find the Pivot table in the Activesheet
When you have a new file the PivotTable will not necessarily have the same name Pivottable1 so that is a new problem.
Recorded macros are fine to start with must you must 'polish' and edit so that things do not get repeated and it runs smoothly.
You should start you macro by selecting the source data and then Insert Pivot table and all the actions you have done so far in tat way the process can be run from scratch but here is where vba knowledge is required so that you name the pivottable yourself and use this as reference not becoming dependent of Excel's automatic naming.
I will see if I can reproduce what you have done but that will not be right away.
Another question is, are you going to copy the module to every new workbook or what have you thought of, what is the idea?
I suggest you lookup some vba tutorials and start at the beginning, VBA is not that difficult once you see what it does, and yes, I started with a recorded macro and started building from there.
Programming takes time and patience and very much of both so know what you're getting into
Like I mentioned I'll see what I can do for you in the meantime

[Keebellah]

BTW what do you mean with "Loop a recorded macro" ? there is no loop
You mean re-use?

[Kaper]

Try keeping this file open, then open also another workbook, where macro shall be applied.
Activate worksheet with pivot table * in this newly open workbook, and run the macro (when you press Alt+F8 or select from ribbon View-Macro) you will see its name preceded by filename, like

BRANDT.xlsm!PTFMT

and run it.If it works, we could discuss looping through more files in automatic way. If not, macro shall be rewritten.

*) as Keebellah noticed above - the macro doees some work with existing pivot table. If you do not have pivot table in other workbook, just the input (raw) data, try registering macro again from the begining of the creation process. The first line (or one of first) shall look somewhat similar to:

Please Login or Register  to view this content.

[Keebellah]

Besides that I suggest that the section to check if a pivot table exists be a little more sophisticated and at the same time define the source table's range, I noticed that a range was defined but it does not cover the entire table in Sheet 1
and misses the last column while the pivot table in Sheet 2 DOES cover the area.

Before you try to make the macro 'multi' useful I suggest that all parameters be written independent of the table name and pivot table name, it's a question of adding error traps to verify what exists and what doesn't so ou can be sure that the data represented is actually the data available.

If you define a source that only applies from row 1 through row 26 but the data goes on to row 39, you're seriously missing 13 rows of data
Back to the drawing table and think it over.

[rcdavis28]

"Another question is, are you going to copy the module to every new workbook or what have you thought of, what is the idea?"

Thank you Keebellah for your input! Yes. I intend to copy the module to every new workbook. That was my hope when I started this thing anyway. Also, thank you for the encouragement. Your explanations make sense as to why I am receiving errors. Thank you for that, and for the learning experience. These are things that I simply did not know existed and problems that I did not know existed. Everyone has told me to start with the macro recorder for learning, but so far, I am underwhelmed by the accuracy of it. But its a start.

" I noticed that a range was defined but it does not cover the entire table in Sheet 1and misses the last column while the pivot table in Sheet 2 DOES cover the area"

To this point.. I apologize for that. I am pulling a report from our ERP system to do my analysis. The report is extremely lacking, and I had to perform many, many steps to clean the data up to get it into seperate fields to analyze. Part of that process was creating a named range. That last column with UOM, I do not need for my analysis. I tried to remember to delete that guy on all of them, but I obviously forgot on that one.

I just want to understand your point. The named range will not be the same for all the workbooks, because some have more data than others etc.. So, my steps should be to do what? If it is too much to explain or if you are done with me , don't bother repsonding. I do not want to take up any more of your time. Thank you for everything you taught me. Also, Kaper thank you for your input as well! I will try your approach and see what I get. Cheers.

1.

[Keebellah]

Hi, don't worry let me put together my idea and I will attach the file with what I hope is an understandable explanation, I'm not that good at that since I build and then have to document but I'll do my best.
I'll use your file for starters and then help you use it for different ones.
I've got an idea and maybe it will help you and you won't need to copy the code at all times.
Sl, you're still stuck with me and I hope that my enthusiasm for VBA will spill-over on you

[Keebellah]

I haven't done anything special but this macro will create and refresh the Picot table
What it does is delete the existing worksheet where the pivot table is and recreate it with the data in Sheet1
The only thing you have to make sure is that the data IS in a worksheet named Sheet1 and run the macro named MAKEPVT

I kept you PTFMT macro but added a reference to pass to it and reduced the code a little.
Give it a shot.

For now you van paste the entire module in a new workbook but I'll get back to you later for another method since I do not think that macro is required for the target file.

Let me know it it works on your side too.

[rcdavis28]

Thank you so much! I will take a look at it and see what happens. I really appreciate all of your effort and advise.

[Keebellah]

I'm working on the final step, will see if I get it finished tonight

[Keebellah]

Okay, I got something that seems to do the job and no more macro copying
I'm off to bed now so I'll see if you tested it since it's about 6 hours earlier in NC

I have attached a zipped file rodavis28.zip containing two files:

Unpack and place the files on your disk (anywhere)

BRANDT.xlam is an addin but you do not have to install it.
BRANDT-Data.xlsx a file containing your data i Sheet1 (no pivot table)

You may place both files in the same folder but that is not necessary

Double click BRANDT.xlam to open and you will see an extra TAB next to Tab Home select it and it explains itself.
Two buttons:
One to Open a File and the other to Refresh / Create the Pivot table.

Have fun

[Keebellah]

Have to you taken a look?