VBA Macro to fill cells with data from other sheets

Hungry Horace

New Member
Joined
Dec 21, 2016
Messages
10
I am new to Excel and learning as I go and hoping someone here can help me with what I believe is a VBA issue.

I am working on a workbook that's purpose is to create a final report for Participants in a course.
Basically on one sheet would be a list of participants, another sheet would be the attendance record for the participants, on another would be the results of the tests/exams the participants took, and so on.
I have a template that takes the data of each individual from the different sheets and creates a final 'Certificate' for each participant that shows their attendance, exam scores, course pass/fail etc.
Whilst I can link the sheets for certain parts of the data for the Certificate I am having trouble making all of it work.
Is someone able to help with this issue? I am happy to post / send the workbook and provide a detailed explanation of exactly what data should appear in each cell of the Certificate sheet.

Thank you in advance for the help .
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You cannot add attachments to posts in this forum. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Assign this macro to the 'Create Sheets' button. The macro should work properly unless there is a duplicate name. For example, in the file you posted, you have 2 occurrences of "John" in column C of sheet "Course Participants". This will generate an error because you can't name two sheets with the same name.
Code:
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim rName As Range
    Dim foundName As Range
    Dim ws As Worksheet
    For Each rName In Range("C5:C24")
        If rName <> "" Then
            Set ws = Nothing
            On Error Resume Next
            Set ws = Worksheets(rName)
            On Error GoTo 0
            If ws Is Nothing Then
                Sheets("Template").Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = rName
                ActiveSheet.Range("C9") = rName
                Set foundName = Sheets("Attendance Report").Range("C:C").Find(rName, LookIn:=xlValues, lookat:=xlWhole)
                If Not foundName Is Nothing Then
                    ActiveSheet.Range("C11") = Sheets("Attendance Report").Range("W" & foundName.Row)
                End If
                Set foundName = Sheets("Grade Report").Range("B:B").Find(rName, LookIn:=xlValues, lookat:=xlWhole)
                If Not foundName Is Nothing Then
                    ActiveSheet.Range("E16") = Sheets("Grade Report").Range("L" & foundName.Row)
                    ActiveSheet.Range("G16") = Sheets("Grade Report").Range("M" & foundName.Row)
                    ActiveSheet.Range("G25") = Sheets("Grade Report").Range("K" & foundName.Row)
                    If Sheets("Grade Report").Range("C" & foundName.Row).Value >= 75 Then
                        ActiveSheet.Range("C16") = Sheets("Grade Report").Range("C" & foundName.Row).Value
                    Else
                        ActiveSheet.Range("C16") = Sheets("Grade Report").Range("G" & foundName.Row).Value
                    End If
                    If Sheets("Grade Report").Range("D" & foundName.Row).Value >= 75 Then
                        ActiveSheet.Range("C18") = Sheets("Grade Report").Range("D" & foundName.Row).Value
                    Else
                        ActiveSheet.Range("C18") = Sheets("Grade Report").Range("I" & foundName.Row).Value
                    End If
                End If
            End If
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
That is great. Thank you so much for your help, I really appreciate it.
I put the code in as you suggested and it does pretty much exactly what I wanted. I have noticed a couple of small issues, but I don't know if the code is the cause or it is something else.
I have uploaded the latest version of the file. The issues are the following.
On the template sheet cell E11 should show the data in Cell V26 from the Attendance report sheet (its currently set to read cell R26 as I was trying ideas and forgot to change it back to V26)
The other issues involve the charts on the template. The Module score chart should display the data from cells C16 & C18 respectively and the Total averages chart should display the data from cell E16

Link to new file:
https://www.dropbox.com/s/wtxvwfzxzceph3q/E-Report TEST 1.xlsm?dl=0

Thank you again for the help
 
Upvote 0
Thanks again for your help, I just checked it out and it all seems to be working apart from the charts. The two charts at the bottom of the named sheets created by the button should use the data from the relevant cells on the template sheet.
For example the module score chart should represent the data from cell C16 & C18 respectively, and the total averages chart should have the data from cell E16.
Currently only the course average chart displays a chart.

Thanks for your help with this I really appreciate it.
 
Upvote 0
I just tried the file that I uploaded in Post# 8 and after running the macro, all the graphs in the newly created sheets update with the correct information (C16 & C18 & E16). Are you trying the macro on a different version of your workbook?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top