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....

 

[Jerry Sullivan]

Hello,

It sounds like you just need to copy the values from each sheet to your Overall sheet. That's a very common task and you can find many examples on this site that show how to do that.

There are two starting points that users typically have.
1. Combine the data from each sheet in a workbook onto one sheet.
2. Combine the data from all workbooks in a folder onto one sheet.

The fact that the worksheets come from the same template will not affect the process...in fact it will make it more likely that the data is consistent.

Please explain if I've misunderstood your question.

 

[Brutium]

Hello and thank you for your prompt reply.

I am not sure if I made myself understood. What will happen in my case is that the teacher will create first of all a list of all the various tools that (s)he will be using, then running the macro the program will open a number of worksheets based on the list that the teacher created. The worksheets are just a copy of the template I was talking about.

What I need to do is make sure that the data that will be keyed in the just created sheets will be automatically copied into the "Overall" sheet I was mentioning. There should be NO teacher's intervention at all. The process should be totally transparent, and the only thing that the teacher will concern (him)herself with will be just entering marks in the various worksheets. Everything else will be taken care of in the "Overall" sheet.

Once again thank you!
Brutium

 

[Jerry Sullivan]

Hi Brutium,

Ok, now I better understand your question. I'd be glad to provide some assistance.

Something will need to trigger the transferring of data from the teachers' worksheet to the Overall summary. Two options that come to mind each have some pros and cons and the best approach will depend on how much you will know and be able to control about the names and locations of the Teachers' worksheets.

Pull the data from the Teachers' worksheets. You would have a macro or button inside your Overall Sheet workbook that would go get all the info from a certain folder. The Teachers don't need to do anything but make sure the file is saved and formatted correctly.

Push the data from the Teachers' worksheets. This would involve using Event macros that would be triggered by the action of the Teacher saving their workbook.

Both approaches assume you are on a shared network. I'd suggest the Pull Option unless the folders and filenames where the teachers' store their workbooks is unknown to you.

 

[Brutium]

Hello Jerry,
Thank you for your reply. I am still LOST.
I am not sure if I am explaining my self properly.
1. How do I apply a template on a blank sheet?
2. How do I pull the data that was placed into this and other templates to fill the "Overall" sheet?

Thanks
Brutium

 

[Jerry Sullivan]

Hi Brutium,

No problem. I think the part that we aren't connecting on is the starting point you have when you want to run the macro that I am helping you to create.

I interpreted (or misinterpreted) from your first post that you want to create a macro that will collect all the averages from the Teachers' sheets and copy them to a separate workbook on a single sheet called "Overall".

The part that's fuzzy to me is what will be the status of things right before this new macro is run?

Will the Teachers' sheets be stored in a single workbook, separate in workbooks, in the same folder on a network?

Also please clarify whether Overall would start as a completely blank sheet or a sheet that has a row of headings that would match those on the Teachers' sheets.

If you are able to post an example of the Teacher's sheet to a download site, or through a Private Message exchange, that would help my understanding a great deal.

Thanks.

 

[Brutium]

Thank you Jerry,
I think I resolved the issue.
The only problem I now have is that whenever a worksheet is created I get an error since the creation of the worksheets start at B1 instead of B3.
This is the macro I am using. How can I tell the macro to start creating the sheets from B3 and lasting to the the Range specified by the "i"?

Sub Addsheets()
Dim LR As Long, i As Long
With Sheets("tools")
LR = .Range("B" & Rows.Count).End(xlUp).Row
LastSheet = ActiveWorkbook.Sheets.Count
For i = 2 To LR
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(i + 1)
ActiveWorkbook.Sheets(i + 2).Name = .Range("B" & (i)).Value
Next i
ActiveWorkbook.Sheets(3).Name = .Range("B").Value
End With

End Sub

Everytime I run the macro it tells me that I have:

Run-time error "1004"
Application-defined or object-defined error

and once I debug it the error is in the

ActiveWorkbook.Sheets(i + 2).Name = .Range("B" & (i)).Value

line.

What am I doing wrong?

Thanks for any help...

Brutium

 

[Jerry Sullivan]

If you make the changes below, it should work as I think you intended

Sub Addsheets()
    Dim LR As Long, i As Long, LastSheet As Long
    With Sheets("tools")
        LR = .Range("B" & Rows.Count).End(xlUp).Row
        LastSheet = ActiveWorkbook.Sheets.Count
        For i = 3 To LR
            ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(i)
            ActiveWorkbook.Sheets(i + 1).Name = .Range("B" & (i)).value
        Next i
        '??? ActiveWorkbook.Sheets(3).Name = .Range("B").value
    End With
End Sub

When you refer to sheets by their Index number as in: Sheets(3), you run the risk that the total sheet count might be less than that Index.

What were you trying to do with the last line that I commented out with '???.

 

[Brutium]

Hello Jerry,
I am still experiencing difficulties with my macro.
When I start, the tabs of the open sheets say

Class, Tools and Master

My macro is

Sub Addsheets()
Dim LR As Long, i As Long, LastSheet As Long
With Sheets("tools")
LR = .Range("B" & Rows.Count).End(xlUp).Row
LastSheet = ActiveWorkbook.Sheets.Count
For i = 3 To LR
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(i)
ActiveWorkbook.Sheets(i + 1).Name = .Range("B" & (i)).Value
Next i
ActiveWorkbook.Sheets(3).Name = .Range("B").Value
End With
End Sub


Once I run the macro I end up with an error message that says

Run-time error '1004':
Application-defined or object-defined error

Once I click on Debug, the program show in yellow highlight, the following line


ActiveWorkbook.Sheets(i + 1).Name = .Range("B" & (i)).Value


and the tabs at the bottom of my worksheet shows that I have open the following tabs:

Class, Tools, Master, Test, and Master(2).


The line that you had commented with '??? from what I understand takes the "Master" sheet and renames it as the first item in my list found in "Tools".

As you can see Master is kept and it is repeated as Master(2). This should not happen.

I hope this clarifies a bit of the issue. I tried to attach png images of the above errors, but I was not successful (sorry).

Thank you once again

Brutium

 

[Jerry Sullivan]

ActiveWorkbook.Sheets(3).Name = .Range("B").Value
....
The line that you had commented with '??? from what I understand takes the "Master" sheet and renames it as the first item in my list found in "Tools".

If your list starts on B3 and you want the code to rename your sheet to the first value on that list then revise this line to read

ActiveWorkbook.Sheets(3).Name = .Range("B3").Value

And... currently your code is taking the value on B3 and using that for the first copy of Master. So you For...next loop should begin with the sheet name in Cell B4

For i = 4 To LR

I'm not positive why that Error 1004 came up where it did when you ran it. Possibly you were trying to rename a file to an invalid sheet name or a sheet that already existed.

Let me know if this works for you after the 2 fixes above.