VBA Advanced Filter causing Excel file to corrupt...

Hi Fellow Excel enthusiasts

I have a workbook with a worksheet containing a single table of data. I am trying to write VBA to perform an advanced filter and to filter the results from applying the search criteria to another worksheet contained in the same workbook.

Here is the Code:-

Please Login or Register  to view this content.

Option Explicit

Private Sub Dyno_Range1()
Dim LastRow As Long
LastRow = Sheet4.Range("Q" & Rows.Count).End(xlUp).Row
Sheet4.Range("a3:z" & LastRow).Name = "Data"
End Sub

Private Sub Dyno_Range21()
Dim LastRow As Long
LastRow = Sheet33.Range("m" & Rows.Count).End(xlUp).Row
Sheet33.Range("a6:r" & LastRow).Name = "_Depreciation"
End Sub

Private Sub Filter_Depreciation()
'
' Advanced Filter on tab depreciation
'
On Error GoTo ErrHandler
Call Dyno_Range1
Sheets("Depreciation").Range("E17").Select
Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"E1:F2"), CopyToRange:=Sheets("Depreciation").Range("A6:r6"), Unique:=False
Range("k7").Select
ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Add Key:=Range("M7"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Call Dyno_Range21
Range("_Depreciation").Select
ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Add Key:=Range( _
"k:k"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Depreciation").Sort
.SetRange Range("_Depreciation")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=12
ActiveSheet.PageSetup.PrintArea = Selection.Address
ErrHandler:
On Error Resume Next
End Sub

Please Login or Register  to view this content.

After I run the code and save the workbook down and then open I am getting a warning message that reads "We found a problem with some content in "filename.xlsm". Do you want to try to recover as mush as we can? If you trust the source of this workbook click yes".

If I click yes the workbook opens. Phew. If I go to the Name Manager and delete the name ranges and then save and close and reopen the issue goes away.

I think the problem stems with my code for advanced filter when the search has no data values in the main data table.

I would really value any help/comments to fix this.

Thanks in advance

Pablos

What happens if you remove On Error Goto ErrHandler?

Pablos

When you remove On Error... are any other errors/problems highlighted?

PS Using error handling can be a good idea but it's better to have something a bit more specific than having an all encompassing On Error... statement at the start of the code.

In excel, Name Manager, can you have two 'refers to' that refer to the same range? I wonder whether this is the problem with my VBA? Any thoughts?

I think I have fixed my VBA. The line of code :-
Sheet33.Range("a6:r" & LastRow).Name = "_Depreciation"

I changed to:-
Sheet33.Range("a6:r" & LastRow + 1).Name = "_Depreciation"

Still interested to hear your thoughts....