Problems With Worksheet Renaming

[Aggie04]

Hello all,
I am new to the world of VBA and just started working on creating macros about 5 weeks ago. I have been watching YouTube videos and reading blogs to help me get going.
I have the code below that stops working when new sheets are created from the template. I realized after the fact and googling my issue that I should have used the sheet CodeName to write the code so that if the name of the sheets changed the code would still work. However, I tried changing it to reflect the CodeName Sheet1 (corresponds to Evaluations) and Sheet2 (corresponds to Template), and when I run the macro I keep getting error codes. How can I incorporate the sheet codename so that even if the sheet names change the code will still function?<!--[if gte mso 9]>

Option Explicit

Sub Comments()

Application.ScreenUpdating = True

Dim Template:confused: As Worksheet
Dim Evaluations:confused: As Worksheet
Dim Nb_Rows As Integer
Dim i As Integer
Dim x, Row As Integer

Set Template = ThisWorkbook.Sheets("Evaluation Form Template")
Set Evaluations = ThisWorkbook.Sheets("Evaluations")

Template.Range("A61:A70").ClearContents

' the table in this example starts in A1
Nb_Rows = Evaluations.[a1].CurrentRegion.Rows.Count
Row = 61 ' first row to input results in Template
x = 0 ' needed to increment

For i = 1 To Nb_Rows
If Evaluations.Cells(i, 1) = Template.[a1] Then
Template.Cells(Row + x, 1) = Evaluations.Cells(i, 29)
x = x + 1
End If
Next i

Set Template = Nothing
Set Evaluations = Nothing

Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub MyComments()

   Application.ScreenUpdating = True
   
   Dim Nb_Rows As Integer
   Dim i As Integer
   Dim x As Long, Rw As Long
   
   
   Sheet2.Range("A61:A70").ClearContents
   
   ' the table in this example starts in A1
   Nb_Rows = Sheet1.Range("A1").CurrentRegion.Rows.Count
   Rw = 61 ' first row to input results in sheet2
   x = 0 ' needed to increment
   
   For i = 1 To Nb_Rows
      If Sheet1.Cells(i, 1) = Sheet2.Range("A1") Then
         Sheet2.Cells(Rw + x, 1) = Sheet1.Cells(i, 29)
         x = x + 1
      End If
   Next i
   
   Application.ScreenUpdating = True
End Sub

For reference it's best to avoid using VBA keywords for variables/sub names as it can cause problems

 

[Aggie04]

Thank you for responding Fluff! I tried your updated macro and when I run the code in the newly created sheets within the same workbook nothing happens.

 

[Fluff]

If you have newly created sheets, check that the codenames are correct

 

[Aggie04]

Not sure if this helps... but I have another macro in module 1 (Macro below). This is the macro I run first that creates the individual spreadsheets within the same workbook. As you can see by design each of the new spreadsheet's has a student name attached to it. Not sure if this is interferring with my second macro in module 2. Each new spreadsheet that is created has the name of the student in the tab.

Option Explicit

Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Evaluation Form Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
    
    Set wsMASTER = .Sheets("Evaluations")                            'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsMASTER.Range("A2:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
    
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   'if sheet does not exist...
            wsTEMP.Copy After:=.Sheets(.Sheets.Count)               '...create it from template
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
        End If
        Next Nm
    
    wsMASTER.Activate                                           'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end
End With

MsgBox "All sheets created"


End Sub

 

[Fluff]

Firstly, when posting code please use code tags (the # icon in the reply window).
Secondly, in you op you said that you wanted to change from using sheet names to sheet codenames.
Are the codenames for the 2 sheets you mentioned correct?

 

[Aggie04]

Yes the code names are correct.

 

[Fluff]

Try stepping through the code using F8 & look at what is happening.
Does it clear the range A61:A70 on sheet2?
What is the value of Nb_Rows?
Does it get inside the if statement?