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]

test it https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/sheets-object-excel

 

[JustJerry2C]

I have read this as well. I just can't seem to input the code to work right....thanks

 

[cerfani]

in order to reference a worksheet... you simple pass the name of the worksheet instead of the index...

Sheets("Sheet1")

another example...

Dim sheetName As String
sheetName = "Sheet1"
Sheets(sheetName)

 

[cerfani]

So why can't you get the name of the sheet and use it?

 

[Learn Excel in Tamil]

Try code will do coding for all the sheets in excel workbook doesn't matter of the sheet name or index number

Sub a()

For i = 1 To Worksheets.Count
Worksheets(i).Select
Next
End Sub

 

[cerfani]

Try code will do coding for all the sheets in excel workbook doesn't matter of the sheet name or index number

Sub a()

For i = 1 To Worksheets.Count
Worksheets(i).Select
Next
End Sub

he could add if functions to this and check the sheet name first and then to proceed appropriately depending what sheet is being referenced in the loop

 

[JustJerry2C]

So why can't you get the name of the sheet and use it?

I have a control sheet that the teachers input the student names, which when done, automatically changes the sheet name to match the student. As my code is written now, it all works well as long as the sheets don't move out of order. I'm still learning this, so do appreciate reading the comments.

 

[JustJerry2C]

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.

 

[JustJerry2C]

he could add if functions to this and check the sheet name first and then to proceed appropriately depending what sheet is being referenced in the loop

I'll investigate this with an IF statement....thanks