I am getting an "Runtime error 1004 Application-defined or object defined error" on the highlighted line when I run this sub
Code:
Private Sub PrintIssues()
Dim i As Long
Set objExcel = New Excel.Application
Set bkWorkBook = objExcel.Workbooks.Add
Set shWorkSheet = bkWorkBook.ActiveSheet
If optAppeal.Value = True Then
shWorkSheet.Range("A1") = "Appeals Issues Log"
Else
shWorkSheet.Range("A1") = "Reopens Issues Log"
End If
shWorkSheet.Range("B3") = "Prov. Name: " & strProvName
shWorkSheet.Range("A4") = "Prov. Number: " & strProvCode
' shWorkSheet.Range("C3") = "FYE: " & cboFYE.Text
If optAppeal.Value = True Then
shWorkSheet.Range("A5") = "Appeal"
Else
shWorkSheet.Range("A5") = "Reopen"
End If
shWorkSheet.Range("C5") = "Number " & gstrAppealNo
shWorkSheet.Range("A8") = "Issue No"
shWorkSheet.Range("B8") = "Issue"
shWorkSheet.Range("C8") = "Analyst"
shWorkSheet.Range("D8") = "Disposition"
shWorkSheet.Range("E8") = "Est. Impact Amount"
shWorkSheet.Range("F8") = "Act. Impact Amount"
shWorkSheet.Range("G8") = "Comments"
Set rngRowStart = shWorkSheet.Range("A10")
bkWorkBook.Worksheets(1).Columns(1).HorizontalAlignment = xlLeft
bkWorkBook.Worksheets(1).PageSetup.Orientation = xlLandscape
bkWorkBook.Worksheets(1).PageSetup.Zoom = False
'in order for these PageSetup things to work, the ZOOM property
'must be set to false
bkWorkBook.Worksheets(1).PageSetup.FitToPagesWide = 1
bkWorkBook.Worksheets(1).PageSetup.FitToPagesTall = 1
bkWorkBook.Worksheets(1).Columns("E:E").HorizontalAlignment = xlCenter
bkWorkBook.Worksheets(1).Columns("F:F").HorizontalAlignment = xlCenter
For i = 1 To lvwIssues.ListItems.Count
'Place each element in the coresponding column
rngRowStart.Offset(0, 0).Value = lvwIssues.ListItems(1).Text 'issue number
rngRowStart.Offset(0, 1).Value = lvwIssues.ListItems(2).Text 'issue description
rngRowStart.Offset(0, 2).Value = lvwIssues.ListItems(3).Text 'analyst
rngRowStart.Offset(0, 3).Value = lvwIssues.ListItems(4).Text 'disposition
rngRowStart.Offset(0, 4).Value = Format(lvwIssues.ListItems(5).Text, "###,#0") 'est impact amt
rngRowStart.Offset(0, 5).Value = Format(lvwIssues.ListItems(6).Text, "###,#0") 'act impact amt
rngRowStart.Offset(0, 6).Value = lvwIssues.ListItems(7).Text
Next
'Next Row
Set rngRowStart = rngRowStart.Offset(1, 0)
'start with first row of recordset display and wedgie down a couple
lngLast = bkWorkBook.Worksheets(1).Range("A10").End(xlDown).Row + 2
bkWorkBook.Worksheets(1).Cells(lngLast, 5).Value = Format(lblEstImpAmt.Caption, "###,#0") 'est impact total
bkWorkBook.Worksheets(1).Cells(lngLast, 6).Value = Format(lblActImpAmt.Caption, "###,#0") 'act impact total
'make sure everything displays properly.
shWorkSheet.Columns("A:BZ").AutoFit
objExcel.Visible = True
End Sub
What VBA mistake am I making this time?