Loop to Find Records Matching Search Criteria then cut and paste between two spreadsheets

I am new to VBA and have been searching online and watching YouTube Tutorials but am having trouble writing the code below and getting it to work. I am working with two spreadsheets in the same workbook.Any help would be appreciated. Please understand that I am a beginner and need some guidance. There is no need for disparaging comments. I have been working on this for over two weeks and just can’t figure it out.

I have a sheet labeled “template” that has a student name in cell A1. The student's name will change but the location of the name will always be in this cell.

In my second spreadsheet labeled “Evaluations” I need to run a loop in column A to find the student name.

If the student name is found during that search, then I need to copy any information in Column AC that corresponds to the rows where the name is found.

Anything that is copied then needs to be pasted into my first spreadsheet “template” in column A row 61-70 and for it to automatically add any additional rows required to fit the rows copied.

Option Explicit

Sub Test()
Dim StudentName As String '(StudentName is a unique identifier)
Dim Template As Worksheet '(this is the worksheet I'm pulling data into)
Dim Evaluations As Worksheet '(this is the sheet I'm pulling data from)
Dim finalrow As Integer
Dim i As Integer
Set Template = Sheets("Evaluation Form Template")
Set Evaluations = Sheets("Evaluations")

'this is where i want to cut and paste to
'getting an error here
 Range("A61:A70").ClearContents

'This is the value I am looking for: getting an error here
 StudentName = Sheets("Template").Range("A1").Value

 'this is the sheet I am searching my value in Column A
 finalrow = Sheets("Evaluations").Range("A10000").End(xlUp).Row

 'once it runs the loop if the student name was found in Column A then I   need it to copy and paste any information in Column 29/AC
'into my Template sheet in Column A row 61
 For i = 2 To finalrow
 If Cells(i, 1) = StudentName Then
 Range(Cells(i, 29)).Copy
 Sheets("template").Range("A61").End(xldown).Offset(1, 0).PasteSpecialxlPasteFormulasAndNumberFormats
 End If
 Next i

 End Sub

Solution

1) You've declared your sheets but you do not make use of it .

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

Then write - Template.Range("A1").Value
Instead of - Sheets("Template").Range("A1").Value

I think you get an error because you haven't specified the sheet:

Write - Template.Range("A61:A70").ClearContents
Instead of - Range("A61:A70").ClearContents

2) If the name of student is unique, you should use the Range.Find method instead of looping on all the rows. It will be much faster.

Returns a Range object that represents the first cell where that information is found. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel

Dim name_rg As range

{...}

' ~ Search name of the student ~
Set name_rg = Evaluation.columns(1).Find(Template.[a1])

If Not name_rg Is Nothing then
   Template.[a61] = Evaluation.cells(name_rg.row, 29)
Else
   MsgBox("No student found")
End If

3) At the beginning add the line below, it will make your code much faster

Application.ScreenUpdating = False

4) At the end of your code, clear the memory and turn back the screen update to True:

Set name_rg = Nothing
Set Template = Nothing
Set Evaluations = Nothing

Application.ScreenUpdating = True

~ Your code should looks like :

 Option Explicit

 Sub Test()
    Application.ScreenUpdating = False

    Dim StudentName As String
    Dim Template As Worksheet 
    Dim Evaluations As Worksheet 
    Dim finalrow As Integer
    Dim i As Integer
    Dim name_rg As range

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

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

    ' ~ Search name of the student ~
    Set name_rg = Evaluation.columns(1).Find(Template.[a1])

    If Not name_rg Is Nothing then
       Template.[a61] = Evaluation.cells(name_rg.row, 29)
    Else
       MsgBox("No student found")
    End If

    Set name_rg = Nothing
    Set Template = Nothing
    Set Evaluations = Nothing

    Application.ScreenUpdating = True
End Sub

Edit :
In case there is multiple students in Template you will need to do a For Loop instead of using the Range.Find solution. Below the modifications:

Sub Test()
    Application.ScreenUpdating = False

    Dim Template As Worksheet
    Dim Evaluations 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
    ' please mind that blank lines might cause issues
    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 Evalutations.Cells(i, 1) = Template.[a1] Then
            Template.cells(Row + x, 1) = Evalutations.Cells(i, 29)
            x = x + 1
        End If
    Next i

    Set Template = Nothing
    Set Evaluations = Nothing

    Application.ScreenUpdating = True
End Sub