Exception Report
 

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

Exception Report
 

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

--
Regards,
Tom Ogilvy

"Jahsonn" wrote in message
...
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