Exception Report

[Jahsonn]

Hi
See Code below, Tom Ogilvy was kind enough to help me this
yesterday.

The Code should copy errors in sheet Emails to
Exception_Reports. I want the code to only copy cells in
columns a,b,c to Exception_Reports Sheet.

I tried adding the line
Set rng2 = rng1.Offset(0, -1).Resize(, 3)
But this is coming up with error Application or Object
Defined Error. Can anyone help me on this?

Also I tried using the
With Worksheets("Email")
So I dont have to select the sheet but this doesnt seem to
be working either. Can this macro not be run like this?

Thanks

Sub Exceptions_Report()
Dim rng As Range, rng1 As Range, rng2 As Range

With Worksheets("Email")
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
On Error Resume Next
Set rng1 = rng.Offset(0, 1).Resize(, 2).SpecialCells
(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng1 Is Nothing Then
Set rng2 = rng1.Offset(0, -1).Resize(, 3)
rng2.Copy Destination:=Worksheets
("Exceptions_Report").Range("A8")
End If
End With

With Worksheets("Exceptions_Report")
Columns("A:A").HorizontalAlignment = xlLeft
End With

With Worksheets("Exceptions_Report").PageSetup
.PrintTitleRows = "$1:$7"
.LeftMargin = Application.InchesToPoints
(0.748031496062992)
.RightMargin = Application.InchesToPoints
(0.748031496062992)
.TopMargin = Application.InchesToPoints
(0.984251968503937)
.BottomMargin = Application.InchesToPoints
(0.984251968503937)
.HeaderMargin = Application.InchesToPoints
(0.511811023622047)
.FooterMargin = Application.InchesToPoints
(0.511811023622047)
.CenterHorizontally = True
.Orientation = xlLandscape
End With

End Sub

 

[Tom Ogilvy]

Looks like you are mixing and matching code, but perhaps:

Sub Exceptions_Report()
Dim rng As Range, rng1 As Range, rng2 As Range

With Worksheets("Email")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
On Error Resume Next
Set rng1 = rng.Offset(0, 1).Resize(, 2) _
.SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng1 Is Nothing Then
set rng2 = Intersect(rng1.Entirerow, .Range("A:C"))
rng2.Copy Destination:=Worksheets( _
"Exceptions_Report").Range("A8")
End If
End With

With Worksheets("Exceptions_Report")
Columns("A:A").HorizontalAlignment = xlLeft
End With

With Worksheets("Exceptions_Report").PageSetup
.PrintTitleRows = "$1:$7"
.LeftMargin = Application.InchesToPoints
(0.748031496062992)
.RightMargin = Application.InchesToPoints
(0.748031496062992)
.TopMargin = Application.InchesToPoints
(0.984251968503937)
.BottomMargin = Application.InchesToPoints
(0.984251968503937)
.HeaderMargin = Application.InchesToPoints
(0.511811023622047)
.FooterMargin = Application.InchesToPoints
(0.511811023622047)
.CenterHorizontally = True
.Orientation = xlLandscape
End With

End Sub