Macro/Code to Go Through Workbook and Build a Summary

Hello and thanks for looking at my thread for help.

I am working on a project for my work. My problem is that I am trying to build a summary page that will be feed from certain pages in the workbook. Problem is there will be times when a page(s) will copied and duplicated and then the totals from that page will not make it to the summary page unless manually steered to it again. I have found a couple Codes up here to try and do what I want, but not having much luck with it.

My major problem is, I DON'T KNOW ANYTHING ABOUT VBA CODING. :-( So I am at a Serious loss and need help and someone to hold my hand, or better pick me up and walk me over the line.

Hopefully I explained what I am trying to do, if not please let me know and I will try to explain it better.


Create Summary Based on Shore Tank Reports. Cell B60. If possilble Pull Name of a Product from a Cell as well to place next to the Total on the Final Summary.

Thanks for all you help.

Thanks for the Reply.

What I need to do is, to be able to fill out the Shore Tank Report. Once that form is filled out, I need to be able to move on to the next Shore Tank Report and fill that one out. Problem is, there is never a set number of shore tank reports you may need, so I have managed to figure out how to record a macro and manipulate it to allow me to duplicate a Gauge Sheet and Shore Tank Blank whenever I hit the button on the Gauge Sheet to create another set. What I want to be able to do is have a macro/code loop through the book and pull the Total at the bottom of the Shore Tank Report, Cell B60, and report it on a Summary Page. Eventually I would like to make it so I can have it pull the Total from the Shore Tank and Pull the Grade Name, Cell F10, and populate the Summary Sheet with it. Then have it loop through the work book again if needed and update those cells on the summary page if values have changes. I would need it to be able to Check the Product names on the Shore Tank, Cell F10, and compare it to others to see if the Names match any other ones so that it would give a total of all grades on a summary report not a total of each Shore Tank Report. There are a couple macros already in the work book that kind of do what I need, but I couldn't manipulate them to correctly to what I want.

Also I don't know if this is even possible or not. But is there a way to make the Tab Name Reflect a Cell Value Name?

Tab Name= Shore Tank (1) Once a user enters a Product Name in Cell F10, have the Tab Name Change to that Cell Value?

Thanks,

Ok, I figured out the Rename Sheets depending on Cell Value. Only issue I have now is that I have to place the button on all the sheets I want it to do that to. I'm not sure how to go about setting it to do it all sheets, based on a conditional loop value? Something like. IF E10="PRODUCT" AND F10="NOTEMPTY" RENAME TAB TO CELL F10.

Ok I have updated my Example Sheet with codes I have tried using.

What I am trying to do:

1.) Have a macro/code loop through the work book and pull from the Summary at the bottom of the Shore Tank Reports and place the values onto a Summary page.

I found a macro that loops through all the tabs and pastes those totals to new summary page every time. What I want to be able to do is, have that Macro/Code loop through and pull the info only fromt the Shore Tank Reports tabs, and Update the Summary page, instead of deleting it everytime and creating a new one.

2.) Have a macro/code Pull the "Times, Date, and Events" from the Time Log tab and place that infomation on the Standard Wire tab also.

I would like to have this code autoformat the Events so that the (Proper) Caseing was implented and so that the Times, Date, and Events were in order from Oldest to Newest.

Please I really need some help on this, I am going nuts trying to make this work.

Thanks again.

I would like to be able to loop through the work book and pull from each shore tank tab and update the summary tab.

The Product number can be a Static Result that is always there if thats possible.

I need it to be able to loop through and be able to recognize if a new shore tank was added, and update the summary accordingly.

I was thinking maybe, have it pull the available Products from the Cover and Add them to the Product row on the Summary.
Then have the loop go through the workbook and Match the Product on the Shore Tank Reports and update that value on the Summary accordingly.

Oh and yes, the Values from B60 and B61 need to be pulled and displayed on the summary sheet.

This is the code I have been playing with I found else where. My problem is, with this code it pulls from all Sheets and also deletes the Summary Sheets and Adds a new one everytime instead of just updating the values.
Any help would be greatly appreacited as I don't understand any of the code, but am trying to figure it out as I go along.

Please Login or Register  to view this content.

Originally Posted by arlu1201

What do you mean by Static result?

Static meaning, I can just type that in and leave them there, as I don't need them to change at all.

Originally Posted by arlu1201

Is it fine if the macro clears the entries in the summary tab, then goes thru each sheet and updates the values freshly?

That would be great, they code I pasted above, deletes the page everytime instead of refreshing it, which is what I am trying to accomplish. I'm sure that if I could figure that out, how to make it refresh only, I could apply this method to many other areas of my Project as well.

Ok I just pasted the formula in the Module, but haven't dug into it yet. It seems as if it loops through the woork book, but doesn't pull the values yet?

Ok, For some reason, the result of the Code is that it will cycle through the Workbook and paste the values in the upper portion of the workbook? Starting at CELL E2 to E6 and the Same for CELLS U2:U6 and X2:X6 and the results are all mangled.

Also is there a way to have it cycle throught he work book, but not pull from hidden or very hidden tabs? I have tabs that are veryhidden so that I can create new Shore Tank and Gauge ticket from a blank one instead of duplicating the visible ones each time.

I have found that if I Linked the Products Names to the Cover page, and then run the Code, the Values will populate the Totals Fields as they should. I need the Code to be able to Recognize that if there is more than one shore tank report with the same PRODUCT in the work book, to SUM up the Total of the two Reports and add them to the summary instead of listing and duplicating two of the same products on the summary output page.

Any suggestions are greatly appreaciated.

Thank you,

Originally Posted by arlu1201

I ran it at my end and it worked fine. Weren't the details populated in the SUMMARY OF QUANTITIES tab?

If the Products fields have something in them, the code will run fine and populate as needed. But if the Products fields are empty and I try to run the code, it will Populate in the wrong areas. Isn't there a way to make it always populate in the correct area whether or not there is something in those fields already?

Originally Posted by arlu1201

Yeah we can handle this in 2 ways - either you can have the values in the hidden sheets blanked out so it doesnt figure in the summary or you can provide the sheet names and i can exclude them from the code.

The Sheet Names are the same as the Others? "Shore Tank" & "Gage Tic" I would need to Rename them to something different, then figure out how to make it so that when I click on the button on the gage tic tab to duplicate it based on the New Names. This shouldn't be difficult, I'm sure I can figure that out. I will get on that and let you know what the New Names are so that they can be exluded.

Originally Posted by arlu1201

Sure, i will provide you additional code to do the summations.

That would be great. Could you possibly put comments in code that tell me what each line does? I would really like to understand and learn this, so that I could someday help someone else up here out as well. I am more of a hands on person, so reading and applying is very difficult for me while trial and error I can usually trudge through and figure out fairly well. Your help is much appreciated, thank you.

Hey sorry about that. I didn't come up with any idea of how to go about it yet. I was thinking chaging the name to something different and then when you click on the make new shore tank button, have it copy the hidden originals and rename them to Shore Tank (1) and Gauge Tic (1) then when you enter the Product Name in the copy have it Rename to Shore Tank "Name of Product", and Gauge Tic "Name of Product". Is that possible? If it is, then the name for the Originals could be anything, something like "Tank Guage Tic" and "Tank Report".

How do I make it copy and then rename the TAB? I don't know the code or how to go about doing that?

---------- Post added at 02:36 PM ---------- Previous post was at 01:23 PM ----------

I figured out how to make it rename the Sheet. But my problem now is, how do I make it rename to "Shore Tank" and then the next copy to "Shore Tank 2" and so on?

I am stuck on this one? I have it set to copy the two sheet together so the links stay together, then I have it to rename the sheets to Shore Tank and Gauge Tic respectively. But now I need it check if Shore Tank and Gauge Tic names exist and if so name the next compied sheets to Shore Tank (2) & Gauge Tic (2) and so on. Any help?

1) Be able to Create a new Set of Tank Template and Gauge Template and rename them to Shore Tank and Gauge Tic.

2) Eventually have a function in the code that will be able to Rename the Sheets Shore Tank + Value of "F10"
and Gauge Tic + "G4" value and if more than one Name of Shore Tank + Value of "F10" and Gauge Tic + "G4" exists have it increment the names by 1 so that it will be named... Shore Tank RBOB & Gauge TIC RBOB, then Shore Tank RBOB (2) & Gauge TIC RBOB (2) and so on and so on. This would be best done if there was a button in the summary that you could press first to cycle through the workbook and rename all Shore Tank Sheets and Gauge Tic Sheets to Shore Tank +"F10" and Gauge Tic + "G4" value.

3) Then be able to have the Summary cycle through the workbook and Tally up the totals of each product.

There is no predetermined amount of Sheets, hence the reason I made a button to creat a new set each time I needed one.

Originally Posted by arlu1201

I have worked out the code for point 3 for you though i guess it needs a lil tweaking, right?

Yeah this works fairly ok, but could use some tweaking to make it more efficient. It use to list duplicate products instead of just one product total.

Regarding 1 & 2, you want to create a new sheet for Tank & Gauge but before that the existing list of sheets should be renamed, right?

If the sheets could just copy the sheets named Tank Template and Gauge Template at the same time so links stayed linked, and then rename them to Shore tank and Gauge tic at first so the user could fill out the form, then once they fill in the product names on the forms, it could then add the product name to the end of the Sheet name, Shore Tank RBOB and Gauge Tic RBOB, that would be great.

I bet this is getting confusing Lets work it out together and we should get some solution.

Nah, would rather ask many questions and try and get it to work the way I would like it to then to just settle and be stuck with something that almost works. ;-)

Thanks again

Did you have any luck figureing out what I was trying to do? I see your pretty busy today, guess we are keeping you pretty busy trying to help us all out, much appreciated.

Not sure what you totally sure what you mean by Automated?

The User will fill out the Cover Page of the Report, which will fill in the Header part of the forms. Then there are going to be multiple areas on each form that the user will have to fill in themselves. Most of the Input will be done on the Gauge Tic which will Feed the Shore Tank Report and then once the user is done filling in that form, they will fill in the empty cells left on the Shore Tank Report, usually just the volumes and roof correction info.

Originally Posted by arlu1201

I guess your last post was all the info i needed to proceed.

You will need 2 macros for this - .

Originally Posted by arlu1201

1. To create a copy of the sheets whenever required and fill in whatever information which can be filled by the macro.

I already created the macro to duplicate the sheets whenever needed. The button is on the Gauge Tic sheet. It duplicates both sheets so the links will remain linked.

I can't figure out how to Rename the sheets and have them auto increment though? :-(
So if the user has multiple Shore Tank Reports with the same product, it will increment the Sheet name by 1 each time theres a duplicate.

example.
Shore Tank RBOB
User Creates New Sheet
Shore Tank RBOB (2)
User Creates New Sheet
Shore Tank RBOB (3)
and so on. This also needs to do the same thing to the Gauge Tic Sheet as well, since they will be linked to each other.

Originally Posted by arlu1201

2. The summary macro which needs to be tweaked.

Yes this is on the right track. ;-)

Using this formula, the links are linked to the Original template, not the copied template? The macro I have created, copies both sheets at the same time, so that the links will update and remain linked to the new Copied sheets.

Here is the code I have made using your code provided. It works as renameing and increment the sheets as I was looking for.
How would I go about having the sheets Rename again, after the User selects the Product name on the Shore Tank Report?

This code works perfectly so far, for Copying and Renameing sheets.

Please Login or Register  to view this content.

Is there a way to always make a Certain Sheet the last sheet in the book? For instance the Summary of Quantities?

---------- Post added at 03:07 PM ---------- Previous post was at 02:58 PM ----------

Originally Posted by midnightorion

Is there a way to always make a Certain Sheet the last sheet in the book? For instance the Summary of Quantities?

CANCEL THIS REQUEST.. Used another part of your code to make it work.

Please Login or Register  to view this content.

I don't see a way to Create a Copy of a Blank Template and have it Rename it from "Template Name" to "Shore Tank Product Name (1)" and so on.

1.) The user has to create the new set of Sheets

2.) Then select which product from the Template they want to use.

Unless....Theres a way to have a Message box come up when you press the button to choose which product you would like the new sheets to be for?

1.) User Presses Button for new sheet

2.) New Sheet Created and Message Box to Prompt User for which Product they wish to use.

3.) New Sheet Renamed to "Shore Tank & User Input"

Is that possible?

Thanks again.

That would be great.

If the Box could automatically populate from the Cover Sheet, from H36 to H41, This is where the user puts the Products to be inventoried to begin with while starting their report, then when they go to create a new sheet and the box pops up asking which Product to use, it could show the products they already provided on the cover. Then when they selected from the box the product Name. The Renaming could take effect just once like you were asking about earlier.

Originally Posted by arlu1201

How would the macro know the products that are already provided on the cover? Will it be shown somewhere?

I'm not sure.

Can the Macro have a set amount of VARIABLES?

Please Login or Register  to view this content.

Is this even possible?

Thank you.

Bump....Bump

Ok thanks. Good night.

You have any luck on the code?

Ok, cool will give it a shot.
Any luck on the Summary issue? Listing duplicate names, instead of tallying up the total of duplicates?

User prompt works great, is there a way to use Radio buttons instead of typing in a response? If not, no big deal.

This is the code after I got done with it.

Please Login or Register  to view this content.

I think once we get the duplicating issue squared away, I should be all set. Again I appreaciate your time and patience for helping me out.

I am trying to take multiple Excel workbooks and merge them into one for summary purposes. What code is best for this? I have tried several and failed. Please help?

Hello sbastin, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Bump Bump Bump

bump bump bump bump

I must be putting the code in the wrong place or something..

A little Help Please...Where should it be placed?

Please Login or Register  to view this content.

You have any luck on the taking care of the duplicating Name and Totals in the Summary?

Originally Posted by arlu1201

Working on that. Did the solution in post 69 help you?

Yes, worked Great and applied it a little further. Thank you much.

Please Login or Register  to view this content.

How are you making out with that?

Sure, give me a couple minutes.

Attachment 181221 Here is the latest Blank so far.

Just goto the index tab and click on add tab a couple times. Or do you want me to add Values to the Sheets? I will do that while waiting for your response.

Ok cool almost done. Give me about 5 more minutes to plug in all the numbers.

Attachment 181226 Here is a sample form.

I am trying to figure out how to do a count of a Specific set of Work Sheets?

Please Login or Register  to view this content.

I understand that the Range will paste the Count of Work Sheets. But if I add "Like "Shore Tank" after like I did above, it just returns "False" as the value instead of a Count of work sheets with the name Shore Tank? What am I doing wrong?

Sure, just use AC:AE and we can then link the values into the summary tab.

That should work?

Please Login or Register  to view this content.

Yes, and we can then have the macro pull in those values if you wish.

Yeah, that would work fine I suppose. Lets try that and see. ;-)

Originally Posted by arlu1201

You cannot do it that way. You will need to use a loop where it goes to each sheet and checks the name and increments the count.
Like this

Please Login or Register  to view this content.

Using that code, how do I make it Paste the Value into a Cell I want? Like Cell "I10" ?

Index Sheet. Cell "F2"

Would be nice to be able to see how many Visible Sheets of a particular kind existed in the workbook.

Nope, what you did there was perfect. Just an overall count of a set of Sheets created.

Thanks.

I tried the code. If I copy a bunch of sheets, just to increase the number of Shore Tanks. It doubles up the Summary Again.

Will try. Sometime the file size limit gets in the way.

Attachment 181304 Try this one. Just need to add the Code to the Module. I was trying a different tactic to see if I could get it to work another way for now.

I am trying to add more Sheet, but the file becomes too large for the forums.

If you just copy, both the gauge tic and shore tank at the same time, and then Hit the Update Summary button, thats when I got the duplicate errors again.

That seems to have done the trick....


Thank you very much.

Solved!!!!

Give this man a COOKIE!!!

Excellent work.

@ midnightorion

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.