Can I Reference Sheet Codenames in VBA instead of the Index?

[JustJerry2C]

I have this spreadsheet I pieced together, and I've got it working pretty well. The one thing I can't seem to figure out through all the reading I've done is how to use the Sheet Codenames in VBA to do what this currently does IF a teacher accidentally moves sheets out of order. It's happened only twice, but I'd like to try and prevent it IF I am able to understand anyone's suggestions

Here is all the code for this button. I am wondering if there is a way to replace the worksheet references that I highlighted in BOLD and UNDERLINED text to use the codenames for the sheets. Each student has two associated sheets, which I used the VBE to rename the codenames to S1ReportCard, S1CheckList for student 1, etc.

Private Sub CommandButton1_Click()
'Code for Blue Column Individual Creation
Dim txtSheet As String
Dim txtSheet2 As String
Dim txtSheet3 As String
Dim txtSheet4 As String
Dim txtSheet5 As String
Dim txtSheet6 As String
Dim mainworkbook As Workbook
Dim cntsheets As Integer
Dim cntPrint As Integer
Dim ws As Integer
Dim intMsgBox As Integer
Dim strSaveName As String
Dim OutputFolderName As String
Dim SID As String
Dim SIDB As String
Dim SIDC As String
Dim SIDN As Integer
Dim cntStudent As Integer
Dim SNC As Integer
Dim SNCN As String
Set mainworkbook = ActiveWorkbook
cntsheets = mainworkbook.Sheets.Count   'Count number of sheets in workbook
cntPrint = cntsheets - 5                'Subtract counter by number of grade sheets (currently 5)
cntPrint = cntPrint / 2                 'Divide by two since two sheets per student
ws = 6                                  'Start First Student at worksheet 6
cntStudent = 0                          'Set Student Count to Zero

SID = "E9"                              'Set First Student Create Report Card Cell
SIDN = 9                                'Set First Student Create Report Card Counter
SIDB = "B9"                             'Set First Student Last Name Cell
SIDC = "C9"                             'Set First Student First Name Cell
SNC = 70                                'Set First Cell for Valid Student Name
SNCN = "A70"

'set grade sheets to default files names to be copied and hidden
txtSheet3 = VocabSpell.Name 'Vocab-Spelling
txtSheet4 = Math.Name 'Math
txtSheet5 = Reading.Name 'Reading
txtSheet6 = ELA.Name 'ELA
Line2:
    'Assign Folder or Drive to save files in
    OutputFolderName = ""
    Set myDlg = Application.FileDialog(msoFileDialogFolderPicker)
    myDlg.AllowMultiSelect = False
    myDlg.Title = "Please Select Default Folder to Save Report Cards to:"
    If myDlg.Show = -1 Then
        OutputFolderName = myDlg.SelectedItems(1)
        'Check to see if \ exists, and if not, add one
        If Right(OutputFolderName, 1) = "\" Then
            Set myDlg = Nothing
            GoTo Line3:
        Else
            OutputFolderName = OutputFolderName & "\"
            Set myDlg = Nothing
        End If
    Else
        'If User does not select folder or drive, ask to be sure
        intMsgBox = MsgBox("You MUST select a default Folder to Save to continue.  Select YES to reselect folder or NO to quit", vbYesNo, "Default Folder Needed")
            Select Case intMsgBox
                Case vbYes
                    GoTo Line2:
                Case vbNo
                     Exit Sub
            End Select
    End If
Line3:
intMsgBox = MsgBox("To Create Report Cards for INDIVIDUAL Students marked YES(Y)in the BLUE COLUMN, Click YES, Otherwise Click NO or CANCEL to abort", vbYesNoCancel, "Create ALL Report Cards at Once?")
Select Case intMsgBox
    Case vbNo
        MsgBox "Process Aborted", vbOKOnly, "No Reports Cards Created"
        
        Exit Sub
        
    Case vbCancel
    
        MsgBox "Process Aborted", vbOKOnly, "No Reports Cards Created"
        
        Exit Sub
        
    Case vbYes
    
        For i = 1 To cntPrint   'Set Counter to cycle through all students
        
            If Range(SID).Value = "Y" Then
                If Range(SNCN) = "" Then
                    MsgBox "Student Number " & i & " Has an Invalid Last and First Name, Report Card Not Created", vbOKOnly, "Invalid Name"
                    GoTo Line1:
                End If
                [U][B]Worksheets(ws).Select
                txtSheet = Worksheets(ws).Name   'Assign sheet1 selected name to use for file name on next line
                strSaveName = OutputFolderName & Worksheets(ws).Range("C3").Value & ".xlsx"
                ws = ws + 1   'advance counter by one to select mating checklist
                Worksheets(ws).Select (False)
                txtSheet2 = Worksheets(ws).Name  'Assign sheet2 with selected checklist sheet name[/B][/U]
    
                cntStudent = cntStudent + 1 'Increase Student Counter as cards are made
                Sheets(Array(txtSheet, txtSheet2, txtSheet3, txtSheet4, txtSheet5, txtSheet6)).Copy
                Worksheets(1).Visible = xlSheetHidden
                Worksheets(2).Visible = xlSheetHidden
                Worksheets(3).Visible = xlSheetHidden
                Worksheets(4).Visible = xlSheetHidden
                ActiveWorkbook.SaveAs strSaveName   'Save new file
                ActiveWorkbook.Close    'close new file
            Else
                [U][B]ws = ws + 2    'Advance two sheets when Student is not selected[/B][/U]
                SIDN = SIDN + 1
                SID = "E" & SIDN[U][B]
                SNC = SNC + 1   'Advance to next student name[/B][/U]
                SNCN = "A" & SNC
                GoTo Line4:
    
            End If
        
Line1:
            'Advance to Next Student
            Range(SID) = "N"
            SIDN = SIDN + 1
            
            SID = "E" & SIDN
            [U][B]ws = ws + 2     'Advance to next student[/B][/U]
            SNC = SNC + 1   'Advance to next student name
            SNCN = "A" & SNC
Line4:
        Next i
        
        If cntStudent = 0 Then
            MsgBox "No Report Card Files Have Been Created", vbOKOnly, "Files Not Created"
        Else
            MsgBox "Excel Files For The/(All) Selected " & cntStudent & " Student(s) Have Been Created", vbOKOnly, "Files Successfully Created"
        End If
        
        'Reselect the control Sheet after report cards created
        ws = 1
        Worksheets(ws).Select
        
        Exit Sub
        
    End Select
End Sub

 

[cerfani]

Yeah ok but regardless you still can change the code in a way to not be dependent on sheet index order. Your macro has a lot going and without the workbook itself to debug it would be time consuming for me to suggest an alternative. Each loop you can check the sheet name of the current index and skip sheets if you need to or whatever. Maybe you shouldnt even use a loop but not sure. It is a matter of reorganizing code though and referencing via name and not index. Try to get your sheet names or something and then have your code use them. Good luck!

Dim sheetNames As New Collection 'easier to use than a standard array, no need to redim
For i = 1 To Sheets.Count
    sheetNames.Add Sheets(i).Name
Next i

'ok now u know the names AND THE ORDER... do whatever u want

 

[Joe4]

You shouldn't have to hard-code the names into the code.
You can dynamically capture their names by looping through your list.

For example, let's say that you have a Summary sheet with has names starting in cell A2 going down column A, and the sheet names match the names in this list.
Here is a simple loop that shows you how to capture the values from that list, and place their name in cell A1 of the appropriate sheet:

Sub MyLoop()
    
    Dim lr As Long
    Dim r As Long
    Dim sn As String
    
'   Find last row in column A on Summary sheet
    lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all names starting on row 2
    For r = 2 To lr
'       Capture sheet name, then select cell A1 with the person's name
        sn = Sheets("Summary").Cells(r, "A")
        Sheets(sn).Range("A1") = sn
    Next r
    
End Sub

 

[cerfani]

So I did try this

If Range(SID).Value = "Y" Then
                If Range(SNCN) = "" Then
                    MsgBox "Student Number " & i & " Has an Invalid Last and First Name, Report Card Not Created", vbOKOnly, "Invalid Name"
                    GoTo Line1:
                End If
                Sheets("Alvarez Omar").Select
                txtSheet = Sheets("Alvarez Omar").Name   'Assign sheet1 selected name to use for file name on next line
                strSaveName = OutputFolderName & Sheets("Alvarez Omar").Range("C3").Value & ".xlsx"
                Sheets("Alvarez Omar Check").Select (False)
                txtSheet2 = Sheets("Alvarez Omar Check").Name  'Assign sheet2 with selected checklist sheet name

Which did work, but if the student name changes, this obviously won't work, so that's why I thought I needed to change the CodeName to reference it that way no matter what the sheet name might be.

So Alvarez Omar should be a string variable that prior to this code executing you pull from wherever it is on the sheet

Maybe the first thing your code should do is grab a collection of all student names and then you perform a loop for each student name

 

[Joe4]

Maybe the first thing your code should do is grab a collection of all student names and then you perform a loop for each student name

Kind of like I demonstrated above...

 

[JustJerry2C]

I got this to work! I moved one sheet way out of order, and it still created the file as it should. Thank you

                 For Each wsc In Worksheets
                    If wsc.CodeName = "S" & i & "ReportCard" Then
                        Set myrws = wsc
                        Exit For
                    End If
                Next wsc
                For Each wsc In Worksheets
                    If wsc.CodeName = "S" & i & "CheckList" Then
                        Set mycws = wsc
                        Exit For
                    End If
                Next wsc
                txtSheet = myrws.Name   'Assign sheet1 selected name to use for file name on next line
                strSaveName = OutputFolderName & myrws.Range("C3").Value & ".xlsx"
                mycws.Select (False)
                txtSheet2 = mycws.Name  'Assign sheet2 with selected checklist sheet name