Exporting Sheets into templated workbook

I have searched and searched about this topic, but haven't come up with anything that is exactly what I need.
I am looking for a command button that will pull specific sheets out of a workbook and put them into a templated workbook that has a cover sheet generated, then re save that as a different name.
Within this workbook i am pulling sheets from, there is originally only one sheet labeled TR. I have a button that copies and creates new sheets from this sheet, which then goes up numerically TR(2), TR(3), and so on. The code I was looking for is for it to grab all the TR sheets and copy them into a templated workbook without the macros or formulas, just the values. Or at the very least just into a new workbook.
Any help would be greatly appreciated, thank you!!!

Please Login or Register  to view this content.

The Code I have in red came up with a Run time error '9' Subscript out of range. I honestly have no clue how to fix that...?

Here you go. The button on the TR labeled create new TR will generate a new TR with a higher number, TR(2) TR(3), etc. I only have one TR on there right now because of the file size.

command button 1 is the button with the code I was referring to

Do you want me to send you the template to?

Lol ok thank you so much for you help!!

Good luck sir!! If you find a way a can better format that TR sheet please let me know, I created it when I was till fairly new to VBA.
Thanks again!! This will be a dramatic change in how we do things if you can make this work right. The amount of time you will save us will be amazing.

Compile Error, Syntax error...I just copied all this formula into the command button I was using and changed the path for the template spreadsheet. It highlighted the Private Sub CommandButton1_Click in yellow

All it seems to be doing is creating another TR within the same workbook...

Lol forgive me I might just be slow this morning, but what does that mean exactly?

Its not doing that on my end for some reason. I believe it does a and b, but does not open the archive workbook nor transfer the TR to the archive workbook. Maybe Im not doing something right? I just changed the file path for the archived workbook...

The file you sent me when I open it still has control buttons on the TR sheet.

Oh yes, it has no buttons. But it does copy to the original sheet, only generating a higher number. There are two TR's on the workbook, TR and TR(2). So when I run the macro it comes up with TR(3) within the same workbook. It doenst do anything to the archived workbook

Ok and all I have to do is change the filepath to the Templated workbook I have already created right?

Ok heres what I got now. It did transfer the TR. However it changes the number that goes with it. So say there is only one TR, when it updated to the template workbook it adds a number. So it shows TR(2), when it should just say TR.

Also, If there is more then one TR on the original workbook it only transfers the one you clicked the command button in. I was hoping for it to transfer all of them at once. Sometimes I have up to 35 TR's in a workbook. Now if that is to much of a headache to code, I would be ok with with it only transferring the TR you're in. I also noticed that the company logo pic didnt transfer and a couple of cells for formatted differently. Mainly the Chemical section right in the middle did not maintain there fill.

Step in the right direction though!

I would prefer it to be labeled TR1, TR2 and on and on. The idea is that I transfer all of the TR's out of the the workbook into a Post Job Report. Its so we can show all the information to the customer we are working for. The rest of the sheets in the workbook are not pertinent to the customer.

Also, the sheets that get transferred to the new workbook I would like to file behind the "Cover" sheet. So its nice and tidy and in order.

Awesome!! Thank you very much

Most excellent.

Now we are getting somewhere! Problem I found though. I have two TR's in the workbook. When I transfer them to the template it saves their sheet name as one number higher then they should. So TR and TR(2) get saved into the template workbook as TR(2) and TR(3).
I noticed that when the code runs you created another TR to do the copying and removing of formulas, it then transfers and deletes. When I watched it do that you can see that the sheet name is one number higher. When I testes I clicked on the button in TR(2). As the code ran it created a TR(3) temporarily. I am only speculating that that might be the issue.
Other then that it looks amazing!!!
Oh one more thing, is there a way you can have it prompt me to save as with the template workbook then close it? That way I can always have a master template with the cover sheet. It will open that document, transfer TR's over, then prompt me to save as so I can create a specific title for it.

THANK YOU AGAIN!!!

OUTSTANDING!!! Works flawlessly. Excellent work!
I have another question, not sure if its possible but I might as well throw it at ya. The Post job Report we generate will also have sheets in it labeled Zone Totals. It basically is a simplified version of the TR with just totals on it. Is there a way to automatically pull that information from the cells and put them into the totals sheets?
Now the difficult part Id imagine is that the number of TR's will vary, so the number of Zone Totals sheets will vary. A Zone Totals sheet will have to be created for every TR that gets transferred in. I will attach a file that shows the Zone Totals sheet.

This request might be a bit easier, The Cover page as info that can be directly pulled from the first TR that gets transferred, can I link those cells somehow? Or have the Cover page auto-populate once the TR's are transferred?

Whoops sorry, here is the file.

That file was just the Zone Totals. I am having issues shrinking down the cover page so under 1mb. give me a sec

here we go. Cover sheet, TR1 and TR1's Totals Page. Some of the info isnt quite matched, I copied and pasted from a couple documents. I can explain in detail though what needs to be on the totals sheet.

Thank you again! You have been tremendous help!!

I do not think that is necessary. Do you?

Just threw them in there so you can see what they look like. The order would be Cover page, then first TR with its respective Zone Total, then TR2 with its Zone Total page and so on.

The Totals have to be broke down into each Zone. However, if there is a way to put each zone all on one sheet but maintain Each Zone I do not see a problem with it. And maybe at the end have a totals/averages column for the entire job, all the zones added up. Im guessing that will probably be a way easier task.

Awesome. That is one thing I have never deviled into is pivot tables. Now I am intrigued...

Simple question i am sure, but how do you make cells from being edited? I know how to lock and protect, but I have seen worksheets before where the cell area is grey, and when you click anywhere you cant select any cell at all, as if they are not there...

Like this, go to the sheet that says JSA, then look off to the right at the grey area where there is text

Right, but when I did that you can still click within a cell, it just beeps at you and says you cannot edit because it is protected. If you notice on that sheet i sent you, you cannot even select any of the cells in the grey area on the right. That's what I am looking for.

Well that was easy lol. Give me a couple I will get you the info you are requesting.

Ok I got all the formatting changed up and all the formulas put in where the right column is pulling its data from.
Now on the left column, where it says Proppants 1-3, Those need to be linked to cells A35-37 on the TR.
The liquid chems need to be linked to cells H22-28 and the drychems linked to H30-31.

I attached the updates example

Oh thats fine. I just stuck formulas in there so you can see where the reference the TR.

How is everything going?