asked on

Create Excel workbook and worksheets from list using VBA

I have a sheet in my workbook (called "grading") that i would like to replicate based on a list that i have (below). I would like to select a subject from my list, then based on my selection have VBA find all the criteria that exist in that list and make a copy of the "grading" sheet for each teacher with the tab name next to their name.
I would like to have this done in a new workbook that is created and not in the workbook i am using.
Example: So if i selected english, the code would create a new workbook with 3 tabs of the "Grading" sheet named Comp, Grammar, Reading. It sounds complicated but i am hoping it is doable.

User generated image

Norie

Could you attach a workbook with example 'grading' sheet?

marku24

ASKER

my environment will not let me attach files. It's just a very basic sheet that is a grid to allow grades to be entered. I just need that sheet replicated for all selected. Basically creating a grade book by class.

Norie

Can you post some sample data?

Martin Liss

Add this to your workbook's Grading sheet. When you double click a value in the subject column the workbook will be created. You can use the attached workbook to test it. Note that it doesn't matter where your Subject column is; the code will find it. An assumption is made however that the column header says "Subject" and that the data starts in row 2.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rngSubject As Range
Dim wsThis As Worksheet
Dim lngLastRow As Long
Dim lngRow As Long

Set wsThis = ThisWorkbook.ActiveSheet

Set rngSubject = Cells.Find(What:="Subject", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
If Not rngSubject Is Nothing Then
    If Not IsEmpty(Cells(Target.Row, rngSubject.Column)) Then
        If vbYes = MsgBox("Create a workbook for " & Target & "?", vbYesNo + vbQuestion, "Create Workbook") Then
            lngLastRow = Range("A1048576").End(xlUp).Row
            Workbooks.Add
            With wsThis
                For lngRow = 2 To lngLastRow
                    If .Cells(lngRow, Target.Column) = Target Then
                        ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
                        ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count).Name = .Cells(lngRow, Target.Column + 2)
                    End If
                Next
            End With
            Application.DisplayAlerts = False
            ActiveWorkbook.Sheets("Sheet1").Delete
            ActiveWorkbook.Sheets("Sheet2").Delete
            ActiveWorkbook.Sheets("Sheet3").Delete
            Application.DisplayAlerts = True
            ActiveWorkbook.SaveAs Target
        End If
    Else
        MsgBox "Please select a subject"
        Exit Sub
    End If
Else
    MsgBox "Subject column not found on worksheet", vbCritical, "Error"
    Exit Sub
End If

End Sub

Open in new window

29205268.xlsm

marku24

ASKER

Thank you for the quick reply. One of the issues i am having is that the user doesn't see the subject list table so cannot double click to store the value and start the process. they will select from a sheet called MENU the subject in a drop down which will then be copied to the same sheet (MENU) in cell A1 (to make it easy). As an example: After selecting the value of English, MENU(A1) will have the value of English. It is from this value that i would need the code to look up the 3 subjects in my reference list that is hidden (on a tab REFERENCE). we will create 3 copies of my GRADING sheet and rename from Grading, Grading(1), Grading (2) to the tab names for Comp, Reading and Grammar.

I did try to replace the variables myself to get to work in your code but it may be a bit over my head. Does my explanation make sense? Thank you

Martin Liss

One of the issues i am having is that the user doesn't see the subject list table..

Where is it?

marku24

ASKER

The list is on a tab that is hidden called Reference. I use a combo box on a tab called Menu so they can select the subject they want to create grading sheets for. I then need 3 exact copies of a sheet called Grading with the names from the table (comp, reading and grammer). All three tabs are an exact copy of the tab called grading.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marku24

ASKER

Wow, thank you so much. That is really nice. Thank you for the design advice as well.
Have a good day.

Martin Liss

Would you like a userform version?

Martin Liss

I have it so I've attached it.

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020
29205268b.xlsm

marku24

ASKER

Thank you Martin, incredibly helpful. I haven't worked much with forms before but may give it a shot on this project. Vwry clean look. Thank you.

Martin Liss

You're welcome. If you have any questions about the code or anything else then let me know.

marku24

ASKER

Hello again martin, so sorry this is very much my fault. Your code works great but when i try to weave into my program it isn't working and i know i am just getting the references wrong. I am able to see the menu items in dropdown, it does ask me if i want to create a new workbook with my subject names it just does not copy the sheets and gets hung up.

This is a snippet of the actual weaved in code. I think i am getting confused because in the example the menu value was A1 on Menu and the lookup values on Reference were also column A. I just tried to swap sheet names and ranges. This is the conversion:

MENU -> ReportingMenu
REFERENCE -> ReportReference
GRADING-> UniversalSummary
on the menu my selected values in dropdown work great and is in cell B18
my lookup on references does start in A1 with the data starting in row 2 (just like example)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsG As Worksheet
Dim lngLastRow As Long
Dim lngRow As Long
Dim wsR As Worksheet

Set wsR = ThisWorkbook.Worksheets("ReportReference")
Set wsG = ThisWorkbook.Worksheets("UniversalSummary")

Application.ScreenUpdating = False

If Intersect(Target, Range("B18")) Is Nothing Then (I CHANGES THIS TO WHERE THE SUBJECT VALUE IS ON REPORITNGMENU)
Exit Sub
End If
If Not IsEmpty(Cells(Target.Row, "B")) Then
If vbYes = MsgBox("Create a workbook for " & Target & "?", vbYesNo + vbQuestion, "Create Workbook") Then
Workbooks.Add
With wsR
lngLastRow = .Range("A1048576").End(xlUp).Row

For lngRow = 2 To lngLastRow
If .Cells(lngRow, Target.Column) = Target Then
wsG.Copy after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count).Name = .Cells(lngRow, Target.Column + 2)
End If
Next
End With
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Sheet1").Delete
ActiveWorkbook.Sheets("Sheet2").Delete
ActiveWorkbook.Sheets("Sheet3").Delete
Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Target
End If
Else
MsgBox "Please select a subject from the dropdown list"
Exit Sub
End If
End Sub

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marku24

ASKER

Perfect!!!! Thank you. I thought i would be able to weave it in myself. I appreciate the extra help you gave on this.