Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an "exception" report from comparing 2 excel files | Excel Discussion (Misc queries) | |||
Sum Based on Exception | Excel Discussion (Misc queries) | |||
WHAT ARE THE CAUSES FOR 'EXCEPTION ERROR ' | Excel Discussion (Misc queries) | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
Exception report in excel | Excel Worksheet Functions |