[Solved] Entries Not Appearing in Row 3 After Clearing the Data on Row 3, Column A

[Abdulwahab]

I have created an Excel database named "Marksheet," where all data entries, including students' scores, are stored from the "Add Student" form. However, I'm encountering an issue: whenever I send an entry from the Userform to the "Marksheet," instead of starting at row 3, column A, the entry appears in another row, even though cell A3 is empty. This issue began after I manually cleared the data in row 3. Could you please help me fix this so that new entries automatically start from the next empty row, even if data in a row was manually deleted?

The second issue concerns an Excel VBA Macro. I wrote code to loop through each record in the "Marksheet" database to generate and print all students' report cards from the "Report_Card" template sheet at once. However, it keeps loading for almost an hour without completing. I want it to stop at the last record in the "Marksheet" database. Additionally, when trying to generate PDF files, I could not find the files in the specified location on my computer.

Could you please assist me with these problems? Thank you so much! Below are the screenshots and the Excel file.

Microsoft Office version: 2010 & 2016.

 

[shamimarita]

Hello Abdulwahab,

To ensure that the data is added to the next available empty row in the "Marksheet" after you've manually cleared row 3, you can use the updated code to find the next empty row in column A and then enter the data accordingly.

Private Sub cmdSave_Click()
    '========================================================
    ' Check if required field is filled
    '========================================================
    If txtAdmNo.Text = "" Then
        MsgBox "Please enter the student Admission No.", vbOKOnly, "Required field!"
        txtAdmNo.SetFocus
        Exit Sub
    End If

    '==============================================================
    ' Code to enter data from text boxes into the worksheet
    '==============================================================
    On Error Resume Next
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Set the worksheet where data will be entered
    Set ws = ThisWorkbook.Sheets("Marksheet")
    
    ' Find the first empty row in column A, accounting for row 3 being manually cleared
    If ws.Cells(3, 1).Value = "" Then
        lastRow = 3
    Else
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    End If

    ' Copying data from the user form to the "Marksheet"
    ws.Cells(lastRow, 1).Value = Me.txtAdmNo.Value
    ws.Cells(lastRow, 2).Value = Me.txtName.Value
    ws.Cells(lastRow, 3).Value = Me.comboGender.Value
    ws.Cells(lastRow, 4).Value = Me.comboClass.Value
    ws.Cells(lastRow, 5).Value = Me.comboSession.Value
    ws.Cells(lastRow, 6).Value = Me.comboTerm.Value

    ' Clear the form fields after submitting
    Me.txtAdmNo.Value = ""
    Me.txtName.Value = ""
    Me.comboGender.Value = ""
    Me.comboClass.Value = ""
    Me.comboSession.Value = ""
    Me.comboTerm.Value = ""

    ' Notify the user
    MsgBox "Data entry has been successfully added.", vbInformation
End Sub

There are a few issues in your GenerateReportCards code that need to be addressed:

• You are setting the PDF file path as output, but you are passing outputpdf (which is not initialized) in the ExportAsFixedFormat method.
• You are copying the template to the temporary sheet multiple times inside the loop, but it should only be done once before the loop starts. Also, you can directly modify the template for each student, then export and revert it to avoid creating a temporary sheet.
• The page break logic needs to be refined so that it does not add a page break after every loop iteration. Since you're exporting to a single PDF, page breaks between student reports will automatically be handled by Excel’s export process.

Updated VBA code:

Sub GenerateReportCards()
    On Error Resume Next
    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim lastRow As Long
    Dim studentRow As Long
    Dim output As String
    Dim pdfName As String
    Dim tempsheet As Worksheet
    Dim outputFolder As String
    Dim studentName As String

    'Set worksheets
    Set wsData = ThisWorkbook.Sheets("Marksheet")
    Set wsTemplate = ThisWorkbook.Sheets("ReportCard")

    'Find the last row with data in the Marksheet
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    'Set output PDF file path and name
    outputFolder = "C:\Documents\" 'Ensure the folder exists or modify this path accordingly
    pdfName = "All_Students_ReportCards.pdf"
    output = outputFolder & pdfName

    'Create a temporary sheet for processing
    Set tempsheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    ' Loop through each student in the Marksheet
    For studentRow = 3 To lastRow
        'Copy the template to the temporary sheet
        wsTemplate.Cells.Copy Destination:=tempsheet.Cells

        'Copy the student data from Marksheet to the template in the temporary sheet
        tempsheet.Range("K6").Value = wsData.Cells(studentRow, 1).Value ' Reg No
        tempsheet.Range("C8").Value = wsData.Cells(studentRow, 2).Value ' Name
        tempsheet.Range("K8").Value = wsData.Cells(studentRow, 3).Value ' Gender
        tempsheet.Range("H8").Value = wsData.Cells(studentRow, 4).Value ' Class
        tempsheet.Range("C9").Value = wsData.Cells(studentRow, 5).Value ' Session
        tempsheet.Range("E9").Value = wsData.Cells(studentRow, 6).Value ' Term

        'Add a page break after each student
        If studentRow < lastRow Then
            tempsheet.HPageBreaks.Add Before:=tempsheet.Rows(tempsheet.UsedRange.Rows.Count + 1)
        End If
    Next studentRow

    'Export the temporary sheet to a single PDF
    tempsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=output, Quality:=xlQualityStandard

    'Delete the temporary sheet
    Application.DisplayAlerts = False
    tempsheet.Delete
    Application.DisplayAlerts = True

    'Notify user of completion
    MsgBox "All students' report cards have been saved as " & pdfName, vbInformation

End Sub

 

[Abdulwahab]

Hello Abdulwahab,

To ensure that the data is added to the next available empty row in the "Marksheet" after you've manually cleared row 3, you can use the updated code to find the next empty row in column A and then enter the data accordingly.

Private Sub cmdSave_Click()
    '========================================================
    ' Check if required field is filled
    '========================================================
    If txtAdmNo.Text = "" Then
        MsgBox "Please enter the student Admission No.", vbOKOnly, "Required field!"
        txtAdmNo.SetFocus
        Exit Sub
    End If

    '==============================================================
    ' Code to enter data from text boxes into the worksheet
    '==============================================================
    On Error Resume Next
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Set the worksheet where data will be entered
    Set ws = ThisWorkbook.Sheets("Marksheet")
   
    ' Find the first empty row in column A, accounting for row 3 being manually cleared
    If ws.Cells(3, 1).Value = "" Then
        lastRow = 3
    Else
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    End If

    ' Copying data from the user form to the "Marksheet"
    ws.Cells(lastRow, 1).Value = Me.txtAdmNo.Value
    ws.Cells(lastRow, 2).Value = Me.txtName.Value
    ws.Cells(lastRow, 3).Value = Me.comboGender.Value
    ws.Cells(lastRow, 4).Value = Me.comboClass.Value
    ws.Cells(lastRow, 5).Value = Me.comboSession.Value
    ws.Cells(lastRow, 6).Value = Me.comboTerm.Value

    ' Clear the form fields after submitting
    Me.txtAdmNo.Value = ""
    Me.txtName.Value = ""
    Me.comboGender.Value = ""
    Me.comboClass.Value = ""
    Me.comboSession.Value = ""
    Me.comboTerm.Value = ""

    ' Notify the user
    MsgBox "Data entry has been successfully added.", vbInformation
End Sub

There are a few issues in your GenerateReportCards code that need to be addressed:

• You are setting the PDF file path as output, but you are passing outputpdf (which is not initialized) in the ExportAsFixedFormat method.
• You are copying the template to the temporary sheet multiple times inside the loop, but it should only be done once before the loop starts. Also, you can directly modify the template for each student, then export and revert it to avoid creating a temporary sheet.
• The page break logic needs to be refined so that it does not add a page break after every loop iteration. Since you're exporting to a single PDF, page breaks between student reports will automatically be handled by Excel’s export process.

Updated VBA code:

Sub GenerateReportCards()
    On Error Resume Next
    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim lastRow As Long
    Dim studentRow As Long
    Dim output As String
    Dim pdfName As String
    Dim tempsheet As Worksheet
    Dim outputFolder As String
    Dim studentName As String

    'Set worksheets
    Set wsData = ThisWorkbook.Sheets("Marksheet")
    Set wsTemplate = ThisWorkbook.Sheets("ReportCard")

    'Find the last row with data in the Marksheet
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    'Set output PDF file path and name
    outputFolder = "C:\Documents\" 'Ensure the folder exists or modify this path accordingly
    pdfName = "All_Students_ReportCards.pdf"
    output = outputFolder & pdfName

    'Create a temporary sheet for processing
    Set tempsheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    ' Loop through each student in the Marksheet
    For studentRow = 3 To lastRow
        'Copy the template to the temporary sheet
        wsTemplate.Cells.Copy Destination:=tempsheet.Cells

        'Copy the student data from Marksheet to the template in the temporary sheet
        tempsheet.Range("K6").Value = wsData.Cells(studentRow, 1).Value ' Reg No
        tempsheet.Range("C8").Value = wsData.Cells(studentRow, 2).Value ' Name
        tempsheet.Range("K8").Value = wsData.Cells(studentRow, 3).Value ' Gender
        tempsheet.Range("H8").Value = wsData.Cells(studentRow, 4).Value ' Class
        tempsheet.Range("C9").Value = wsData.Cells(studentRow, 5).Value ' Session
        tempsheet.Range("E9").Value = wsData.Cells(studentRow, 6).Value ' Term

        'Add a page break after each student
        If studentRow < lastRow Then
            tempsheet.HPageBreaks.Add Before:=tempsheet.Rows(tempsheet.UsedRange.Rows.Count + 1)
        End If
    Next studentRow

    'Export the temporary sheet to a single PDF
    tempsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=output, Quality:=xlQualityStandard

    'Delete the temporary sheet
    Application.DisplayAlerts = False
    tempsheet.Delete
    Application.DisplayAlerts = True

    'Notify user of completion
    MsgBox "All students' report cards have been saved as " & pdfName, vbInformation

End Sub

Thank you so much!

 

[shamimarita]

You are most welcome. Keep contributing to ExcelDemy forum and help each other to build a strong Excel community.