copy pivot table data to next available sheet in a certain workbook

So here is my code:

Please Login or Register  to view this content.

In "Template1.xlsm" a quantity is entered into column H on the Inventory Sheet. The code above is assigned to a button called "Run Report", which copies a pivot table from one workbook (template1.xlsm) and places it into a new workbook on sheet1 in A2. These pivot table are going to be created at the least, once a week. There are also going to be numerous pivot tables for numerous job numbers. For example, Job# 10022 consists of 40 pivot tables, Job# 10023 consists of 10 pivot tables,etc. Id like to use the code above or any other to do this:

When Button (Run Report) is clicked i want the pivot table copied to a new sheet in a new workbook (I have that down). With the code above I have the option to save and close this new file with a certain job# ie 10011.xlsm. The next day the Template1.xlsm file is opened and more data needs to be entered into that job. Instead of clicking "Run Report" and having a new workbook created i want the data copied to that same file (10011.xlsm), but to a new sheet. These xlsm files will be stored in a Job Transfers folder on our server.

Here are my thoughts. In the Template1.xlsm file i have 2 sheets, "Inventory" and "Report". I want to create a cell on the "Inventory" sheet that i can enter a job number in, say N1. When "Run Report" is clicked the report is ran, excel recognizes if that job number.xlsm file has been created if true, insert the copied data to the next available sheet/prompt to rename the sheet/save and close. If false, insert the copied data into the first sheet of a new workbook/prompt to rename the sheet/save as (job number entered in N1 on Inventory sheet) and close. I have been searching and searching for weeks now to try to get this done. This seems like alot of work and I just hope someone can help me.

Thanks in advance!

I tried attaching my file but its about 5mb. sorry

It would be good if you attach a sample file. Do no attach the original. If the sample file is too huge (due to the pivot), then zip it.

This is a huge task which i suggest you break up into small manageable bits and then post questions accordingly. No one might be ready to do the whole project for you in one chunk as they wont have that much time. But if you break it into chunks, it can be easily handled.