Transfer data from template

[Brutium]

Hello,
I have a template which calculates in a certain column the averages of marks that students get on assignments. I have a macro which opens a number of sheets which are named according to the various tools for assessment that a teacher uses in class. All of the sheets use the same template. My question is: is there a way that I can collect all the averages calculated in the sheets that I opened (eventhough they use the same tamplate) on a sheet that I called "Overall"? I would need to set up the Overall sheet so that a teacher can add sheets and the marks get recorded on the Overall sheet without the user having to do any adjustment.
Any help would be greatly appreciated....

 

[Brutium]

Hello Jerry,

Yes you were correct. I did not realize that in fact I did have some data in the cell located at B12 and that is the reason it was creating the additional worksheets. The "Immediate" window shows that it did create the additional sheets but it is not renaming them.

Rename Sheet: 4 Now: MasterSheet (2) to: Quizzes
Rename Sheet: 5 Now: MasterSheet (3) to: Homework

Also from what you can see it did not rename the original MasterSheet.

I removed the '(comment) from the the three lines of code you sent me earlier and now EVERYTHING seems to be working fine!!!!


Thank you So MUCH!!!
You are the GREATEST!!!!!

One last question
I am not sure if I should bother you by taking much more of your time, but
Is it possible to place the name of the newly created worksheets in a cell let's say P5?

THANK YOU A LOT!!!

 

[Jerry Sullivan]

I'm delighted to hear that it worked for you!

Is it possible to place the name of the newly created worksheets in a cell let's say P5?

Since the new sheets match your list, would that be just a matter of copying the range at B3:B(LR) to a range beginning at P5? Or were you wanting to merge the names into a single cell, or something else?

 

[Brutium]

Good morning Jerry,

Sorry I was not able to reply sooner.

No, what I would like to do is to have the title of each newly created WorkSheet placed in a cell.

What I mean is:
Through the macro we created worksheet a, worksheet b, worksheet c etc. this is what I had specified in "Tools" range B3 to "i". Now, what I would like to accomplish is place the "a", "b", "c" etc. on each of the correspondinig sheets at position P5.
I hope I made myself clear.

Thank you

Brutium

 

[Brutium]

Good Morning Jerry,
Would it be possible to accomplish what I need if I were to use the following:

Function TabName()
TabName = ActiveWorkbook.Sheet.Name
End Function

and placing in P5 the following:

=TABNAME()

I tried, but it returns with the same sheet name for all newly created sheets. How do I correct this?

Brutium

 

[Brutium]

Hello Jerry once again!!!!

eventhough it is NOT too elegant, I solved the problem. I accomplished this by placing in P5 of my template (WorkSheet) the following:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I found this piece of code while surfing the net, I do not know why I need to place the ",A1", ,but it works.

Once again
Thank you for all your help. It is VERY much appreciated.

Brutium

 

[Jerry Sullivan]

Hi Brutium,

Great to see that you found a solution. The macro could have been revised to add the names to cell P5 of each sheet as they are created...but I like your solution better since it will automatically change if the sheet is renamed.

Nice going!

 

[Brutium]

Good evening Jerry,

I seem to have solved my initial problem but now I am confronted with another, to which I do not have any idea on how to go about.
Now that I have created my new worksheets, how do I get the data to talk to my Main (Summary) sheet? Any ideas?

Thanks

Brutium

 

[Jerry Sullivan]

Hi Brutium,

It's probably best if you start a new thread for this question. That makes it easier for people to follow in the future if they have a similar question.

You might take a look at this post first, and try to adapt the concept.
http://www.mrexcel.com/forum/showthread.php?t=520618

If you get stuck, you can start a new thread describing what you have tried and what problem you are encountering. I'll keep an eye out for it and odds are someone else will be able to help you sooner.

Good luck!

 

[Brutium]

Once again
THANK YOU for all your help.
I will start a new thread, as you suggested.

Have a wonderful day!

Brutium