Excel 2007 : How do I combine 6 worksheets into one mastersheet in the same workbook?

[jahteacher]

I am a complete newbie in Excel, and I am trying to figure out how to combine 6 tabs in one workbook onto one master sheet within the workbook.

Worsheet names are GR 2 TEACHER 1, GR 2 TEACHER 2, GR 2 TEACHER 3, GR 2 TEACHER 4, GR 2 TEACHER 5, GR 2 TEACHER 6

The Master sheet is titled Master Sheet and is the 1st tab.

All worksheets have the same number of columns, but are currently empty. This is meant to be a template that will compile student data by grade level. When a teacher enters their data on their tab, I would like for it to update the Master Sheet.

I have seen multiple "codes" but even when I enter the VBA, I am not sure where/how to begin entering codes.

I would appreciate any help that could be provided. Using Excel 2007

[MarvinP]

Hi jahteacher and welcome to the forum,

I did a very similar problem using VBA code in thread
http://www.excelforum.com/excel-gene...ta-tables.html

Read the code to see what was done and then, if you need more help, supply a sample workbook with your layout for the classes and master and I'll modify my code.

[jahteacher]

Like I said, I haven't the slightest clue as to how/what to do with programming in excel. Outside of creating mirror cells for the master sheet, I am clueless!

I have attached a sample workbook that would be used. I simply need all of the student names, scores (data from all of the columns) to transfer to the master sheet.

I can't begin to tell you how VERY appreciative I am of your time and assistance. This will help all of the teachers in my district be able to immediately use the data to better help our kiddos.

Thanks so much for your help!

[MarvinP]

OK jahteach (in Dispair)

Find the attached that has a VBA Macro that will move all your teacher class sheets onto the master. Look at the macro code and see if you can understand it a little.

I think you should insert a column A in your master that tells which class the students are from or perhaps the teacher's name. If you want a change I can fix.

Let me know if this works for you.

[jahteacher]

MarvinP,

WOW! This is AMAZING! Looking at the VBA Macro, I understand what you have done, or at least I think so. :0)

(In Despair/ In Indiana... It's all the same thing... Let's add incapable of typing the correct letters in my original location "Dispair... shows how frazzled I was with my attempts to figure this out...)

I agree that inserting a column A would be wonderful. What would I need to do to accomplish this?

If I save this as a template, can I just add and delete tabs in order to accommodate grade levels with differing numbers of teachers?

Again, I certainly appreciate the time that you have taken to help me with this. The [I]Excel Bible[I] has nothing on you!!!

OK jahteach (in Dispair)

Find the attached that has a VBA Macro that will move all your teacher class sheets onto the master. Look at the macro code and see if you can understand it a little.

I think you should insert a column A in your master that tells which class the students are from or perhaps the teacher's name. If you want a change I can fix.

Let me know if this works for you.

[MarvinP]

Hi,

I'm glad you read and saw the power of VBA, although I don't expect you to be able to do it yourself yet.

I had a problem with Row 5 on the Teachers sheets not being the same as Row 5 on the Master. My macro was not good on this account. I need help from you to see if Row 5 on the Master should be the same as the other sheets.

YES - you can add as many teacher sheets as you want and the macro will grab the data and append it to the bottom of the Master.

My biggest concern is that computers are supposed to make things easier. It looks like you have WAY too much data entry to do. I'm hoping this is a once a week project and not daily. Then I'm hoping you only need to enter a few numbers instead of all of them for each student.

Explain how this works so I can make the data give more answers and be easier to input.

Let me know and I'll keep working on it.

[jahteacher]

I see that I screwed up the master when I set it up, and I omitted row 5. That should be a part of the Master.

That was my error, your macro is perfect!

I agree that this is a MASSIVE amount of data entry, but it is not all at once. I dump all of the kiddos' names in for teachers, and most students will only need to have 3 scores entered throughout the year.

What you have done with the macro will empower teachers to be able to have their data immediately to help kiddos who might be "at risk" for failure. (That's what the CRAZY conditional formatting is all about.) Green = Good to go; Red= We need to provide strategic intervention; blue= kiddo has achieved the 75%th percentile nationally (Blue, blue, sail on through- we don't need to do the final "snapshot" assessment)

I shared the spreadsheet layout with a group of teachers today, and one actually got teary-eyed because what you have helped accomplish will literally save THOUSANDS of teacher hours throughout our district. (and I provided you with the due credit for making the impossible possible and for saving me the laborsome task of creating mirror cells which would have taken me forever).

Too many times we teachers get a bad wrap for not doing enough to help kiddos. In this case, you are helping me to remove a MAJOR barrier for teachers by taking something off of their plate. (The current spreadsheet in use was designed by someone with minimal experience with Excel who doesn't actually have to use the information in the spreadsheet, and most likely had not spent anytime in the classroom in the last 15 years...) I have just enough knowledge of Excel to be dangerous; I'm just the sort that chooses to look for ways to remove barriers rather than perseverating on the fact that "things shouldn't be this hard." (They used to have us sort the kids by data columns and physically "fill" the appropriate cells with the appropriate color...)

Again, I can't express enough appreciation for helping me to help teachers have more time to focus on what matters most- kiddos.

Hi,

I'm glad you read and saw the power of VBA, although I don't expect you to be able to do it yourself yet.

I had a problem with Row 5 on the Teachers sheets not being the same as Row 5 on the Master. My macro was not good on this account. I need help from you to see if Row 5 on the Master should be the same as the other sheets.

YES - you can add as many teacher sheets as you want and the macro will grab the data and append it to the bottom of the Master.

My biggest concern is that computers are supposed to make things easier. It looks like you have WAY too much data entry to do. I'm hoping this is a once a week project and not daily. Then I'm hoping you only need to enter a few numbers instead of all of them for each student.

Explain how this works so I can make the data give more answers and be easier to input.

Let me know and I'll keep working on it.

[MarvinP]

Hi and thanks for the good grade you have given me.

First, I'm relieved that you aren't going to enter a number in each cell for all students for each week.

Second - We can do more stuff with your workbook design that will allow you to get more answers. I'm thinking Pivot Tables or List/Table sorting and filtering.

Back when I taught high school math, I wondered if my grades were meaningful. I did a study within our math department. I realized my grading scale was a little harder than the average department grade. I moved my scale a little so the grading of the department was a lot more consistent. Tools like what are developing should be used directly for the kiddos as you call them. Education should be for the kids and not the teachers or principals or board of education. The more information you can gather about students and how they learn or what teacher best caters to what type of student is effort well spent.

Please feel free to continue to ask for help with Excel. Most all of us are ready and willing to help.



.

[jahteacher]

Thank you again for ALL of your help. I have been able to use the code to create working documents for all 3 of my grade levels.

One last question: Is there a way that I can protect the Master Sheet so the cells will not alllow data to be entered, but the worksheet will still be able to update when new data is entered on the individual tabs?

Hi and thanks for the good grade you have given me.

First, I'm relieved that you aren't going to enter a number in each cell for all students for each week.

Second - We can do more stuff with your workbook design that will allow you to get more answers. I'm thinking Pivot Tables or List/Table sorting and filtering.

Back when I taught high school math, I wondered if my grades were meaningful. I did a study within our math department. I realized my grading scale was a little harder than the average department grade. I moved my scale a little so the grading of the department was a lot more consistent. Tools like what are developing should be used directly for the kiddos as you call them. Education should be for the kids and not the teachers or principals or board of education. The more information you can gather about students and how they learn or what teacher best caters to what type of student is effort well spent.

Please feel free to continue to ask for help with Excel. Most all of us are ready and willing to help.



.

[MarvinP]

Hi,

1st - you don't need to quote on this forum as we can see what was said above and it makes it a lot more readable.

2. The first thing I do is to clear the entire Master sheet so anything that was on it is immediately lost. Soo.. I don't see where protecting it would do anything but make the macro complain.

3. You could protect the entire master sheet and put a line or two of code in the macro. The first would unprotect the sheet and down at the bottom it would protect it again.

Another site claims that:

Please Login or Register  to view this content.

are the two lines of code you need.

Hope that helps.