Hello,
I was wondering how to circumvent the following error. I am repeatedly using Advanced filter to sort my data into different worksheets. I do this about 12 times. However, depending upon the week, certain criteria will be met by no data. I recieve an error on the .Showalldata line, because no data remain after being sorted and deleted from the main page.
Here is a section of my code to give you an idea. Thanks for the help.
I was wondering how to circumvent the following error. I am repeatedly using Advanced filter to sort my data into different worksheets. I do this about 12 times. However, depending upon the week, certain criteria will be met by no data. I recieve an error on the .Showalldata line, because no data remain after being sorted and deleted from the main page.
Here is a section of my code to give you an idea. Thanks for the help.
Code:
Dim rngSource As Range
Dim EIO As Worksheet
Dim EndColumn As Long
Dim EndRow As Long
Set EIO = ActiveSheet
EndColumn = EIO.Cells("1", Columns.Count).End(xlToLeft).Column
EndRow = EIO.Cells(Rows.Count, "A").End(xlUp).Row
Set rngSource = EIO.Range(EIO.Cells(1, 1), EIO.Cells(EndRow, EndColumn))
'LT 6 Mos Closed
With rngSource
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Advanced Filter Criteria").Range("B2:D3"), Unique:=False
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("LT 6 Mos Closed Detail").Range("a1")
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.ShowAllData
Set rngSource = Nothing
EndRow = 0
EndColumn = 0
'LT 6 Mos Assigned
EIO.Rows("1:1").insert Shift:=xlDown
Worksheets("LT 6 Mos Closed Detail").Rows("1:1").Copy _
Destination:=EIO.Range("A1")
EndColumn = EIO.Cells("1", Columns.Count).End(xlToLeft).Column
EndRow = EIO.Cells(Rows.Count, "A").End(xlUp).Row
Set rngSource = EIO.Range(EIO.Cells(1, 1), EIO.Cells(EndRow, EndColumn))
With rngSource
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Advanced Filter Criteria").Range("B9:D10"), Unique:=False
.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Worksheets("LT 6 Mos Assigned Detail").Range("a1")
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
[COLOR=red]EIO.ShowAllData[/COLOR]
Set rngSource = Nothing
EndRow = 0
EndColumn = 0