PDA

View Full Version : Excel Crashes



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

SamT
06-11-2018, 01:46 PM
you have so many "This.Activate" and "That.Copy" that there is no telling what is active at any given point in the code

First

Dim IndRep As Worksheet
Dim StuDat As Worksheet
Set IndRep = Sheets("Individual Report")
Set StuRep = Sheets("Student Data")

Thereafter precede every Range with either
IndRep.Range(...
'or
StuDat.Range(...

Then, you can replace all
Dot Activate
ActiveCell Dot PasteSpecialWith

[StuDat|IndRep] Dot Range(...) Dot PasteSpecial

elsone31
06-11-2018, 02:16 PM
Great, I will give this a try and let you know how it turns out.

Thank you.