How To Loop A Recorded Macro

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

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

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:

    ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache. _
        CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:= _
        "Pivot Table1", DefaultVersion:=6

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.

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

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.

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

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

Have to you taken a look?