elsone31
06-11-2018, 01:05 PM
What in my code is causing Excel to crash? I turned the command red that causes the issue. I can move the copy up in front of the "If" statements and not have the issue, have I written them in a way that causes this issue? I can run other macros just fine.
I am using excel 2013.
Thanks in advance for any and all help.
Sub Individual_Reports()
With Worksheets("Student Data")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
'MsgBox lastrow
End With
'Dim eID As String
'eID = InputBox("Enter your eID")
'Delete Folder
'folderpath = "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS"
'Set FSO = CreateObject("Scripting.FileSystemObject")
'If FSO.FolderExists(folderpath) = True Then
'FSO.deleteFolder (folderpath)
'End If
For i = 7 To lastrow
'Make Folder
'MkDir "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS"
'MkDir "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS\Individual Reports"
'Adding and Subtracting Mentally
Worksheets("Individual Report").Range("B3").Value = Worksheets("Student Data").Cells(i, 1).Value 'Student Name
Worksheets("Individual Report").Range("E11").Value = Worksheets("Student Data").Cells(i, 4).Value 'Question 1
Worksheets("Individual Report").Range("E12").Value = Worksheets("Student Data").Cells(i, 8).Value 'Question 2
Worksheets("Individual Report").Range("E13").Value = Worksheets("Student Data").Cells(i, 12).Value 'Question 3
Worksheets("Individual Report").Range("E14").Value = Worksheets("Student Data").Cells(i, 16).Value 'Question 4
If Sheets("Student Data").Cells(i, 43) = "" Then
Else
Sheets("Student Data").Cells(i, 43).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 44) = "" Then
Else
Sheets("Student Data").Cells(i, 44).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 45) = "" Then
Else
Sheets("Student Data").Cells(i, 45).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
'Multplying and Dividing
Worksheets("Individual Report").Range("E16").Value = Worksheets("Student Data").Cells(i, 20).Value 'Question 5
Worksheets("Individual Report").Range("E17").Value = Worksheets("Student Data").Cells(i, 24).Value 'Question 6
Worksheets("Individual Report").Range("E18").Value = Worksheets("Student Data").Cells(i, 28).Value 'Question 7
Worksheets("Individual Report").Range("E19").Value = Worksheets("Student Data").Cells(i, 32).Value 'Question 8
If Sheets("Student Data").Cells(i, 46) = "" Then
Else
Sheets("Student Data").Cells(i, 46).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 47) = "" Then
Else
Sheets("Student Data").Cells(i, 47).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 48) = "" Then
Else
Sheets("Student Data").Cells(i, 48).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 49) = "" Then
Else
Sheets("Student Data").Cells(i, 49).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
Worksheets("Individual Report").Range("E21").Value = Worksheets("Student Data").Cells(i, 36).Value 'Question 9
Worksheets("Individual Report").Range("E22").Value = Worksheets("Student Data").Cells(i, 40).Value 'Question 10
If Sheets("Student Data").Cells(i, 50) = "" Then
Else
Sheets("Student Data").Cells(i, 50).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 51) = "" Then
Else
Sheets("Student Data").Cells(i, 51).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 52) = "" Then
Else
Sheets("Student Data").Cells(i, 52).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
Dim Path As String
Dim FileName As String
Range("A1").Select
Sheets("Individual Report").Copy
Path = "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS\Individual Reports"
FileName = Range("B3")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users" & eID & "\Desktop\PMATH REASONING REPORTS\Individual Reports" & FileName
'ActiveWorkbook.SaveAs FileName:=Path & FileName
ActiveWorkbook.Close SaveChanges:=False
Worksheets("Individual Report").Range("H11:H14").ClearContents
Worksheets("Individual Report").Range("H16:H19").ClearContents
Worksheets("Individual Report").Range("H21:H22").ClearContents
Next i
MsgBox ("Your Reports Are Finished!")
End Sub
I am using excel 2013.
Thanks in advance for any and all help.
Sub Individual_Reports()
With Worksheets("Student Data")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
'MsgBox lastrow
End With
'Dim eID As String
'eID = InputBox("Enter your eID")
'Delete Folder
'folderpath = "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS"
'Set FSO = CreateObject("Scripting.FileSystemObject")
'If FSO.FolderExists(folderpath) = True Then
'FSO.deleteFolder (folderpath)
'End If
For i = 7 To lastrow
'Make Folder
'MkDir "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS"
'MkDir "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS\Individual Reports"
'Adding and Subtracting Mentally
Worksheets("Individual Report").Range("B3").Value = Worksheets("Student Data").Cells(i, 1).Value 'Student Name
Worksheets("Individual Report").Range("E11").Value = Worksheets("Student Data").Cells(i, 4).Value 'Question 1
Worksheets("Individual Report").Range("E12").Value = Worksheets("Student Data").Cells(i, 8).Value 'Question 2
Worksheets("Individual Report").Range("E13").Value = Worksheets("Student Data").Cells(i, 12).Value 'Question 3
Worksheets("Individual Report").Range("E14").Value = Worksheets("Student Data").Cells(i, 16).Value 'Question 4
If Sheets("Student Data").Cells(i, 43) = "" Then
Else
Sheets("Student Data").Cells(i, 43).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 44) = "" Then
Else
Sheets("Student Data").Cells(i, 44).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 45) = "" Then
Else
Sheets("Student Data").Cells(i, 45).Copy
Range("H14").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
'Multplying and Dividing
Worksheets("Individual Report").Range("E16").Value = Worksheets("Student Data").Cells(i, 20).Value 'Question 5
Worksheets("Individual Report").Range("E17").Value = Worksheets("Student Data").Cells(i, 24).Value 'Question 6
Worksheets("Individual Report").Range("E18").Value = Worksheets("Student Data").Cells(i, 28).Value 'Question 7
Worksheets("Individual Report").Range("E19").Value = Worksheets("Student Data").Cells(i, 32).Value 'Question 8
If Sheets("Student Data").Cells(i, 46) = "" Then
Else
Sheets("Student Data").Cells(i, 46).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 47) = "" Then
Else
Sheets("Student Data").Cells(i, 47).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 48) = "" Then
Else
Sheets("Student Data").Cells(i, 48).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 49) = "" Then
Else
Sheets("Student Data").Cells(i, 49).Copy
Range("H19").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
Worksheets("Individual Report").Range("E21").Value = Worksheets("Student Data").Cells(i, 36).Value 'Question 9
Worksheets("Individual Report").Range("E22").Value = Worksheets("Student Data").Cells(i, 40).Value 'Question 10
If Sheets("Student Data").Cells(i, 50) = "" Then
Else
Sheets("Student Data").Cells(i, 50).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 51) = "" Then
Else
Sheets("Student Data").Cells(i, 51).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
If Sheets("Student Data").Cells(i, 52) = "" Then
Else
Sheets("Student Data").Cells(i, 52).Copy
Range("H22").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
Dim Path As String
Dim FileName As String
Range("A1").Select
Sheets("Individual Report").Copy
Path = "C:\Users" & eID & "\Desktop\MATH REASONING REPORTS\Individual Reports"
FileName = Range("B3")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users" & eID & "\Desktop\PMATH REASONING REPORTS\Individual Reports" & FileName
'ActiveWorkbook.SaveAs FileName:=Path & FileName
ActiveWorkbook.Close SaveChanges:=False
Worksheets("Individual Report").Range("H11:H14").ClearContents
Worksheets("Individual Report").Range("H16:H19").ClearContents
Worksheets("Individual Report").Range("H21:H22").ClearContents
Next i
MsgBox ("Your Reports Are Finished!")
End Sub