0

I'm using an advanced filter to copy data from a main worksheet 'SDC' and adding into different workbook tables with fewer headings. The filter criteria are created on a different sheet.

Now, I use the same code for the same data, but adding it into other workbook tables earlier in the code, and it works absolutely fine. Last week the same code worked for this new workbook, but today it decided to start giving errors.... without having changed any code.

I've spent 6 hours trying to figure out the problem, but no success yet.

I did a couple of tests to see where the issues come in:

  • When I use the same headings as the destination table (but put them on a new sheet), and manually do the advanced filter, It works fine.
  • When I tried manually doing the advanced filter to the one destination workbook, it worked, and then the next time after that nothing happens. Very strange...
  • I've checked all the headings to make sure they match, no spaces anywhere. Still, nothing gets added to the destination workbook tables when I run the advanced filter manually.
  • I've checked the ranges in the watch window when the macro runs step by step, and everything seems fine. (until the error comes up with the advanced filter)

I don't understand how it worked before, and suddenly starting giving errors. Then it worked for one table and then not. It seems to be this one workbook that has issues or maybe because I'm using multiple criteria for one column? (but it works manually). What am I missing?

I get an error when the advanced filter has to run in the macro: Run-time error '1004': AdvancedFilter method of range class failed on the code line

SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=OOSdirectCrit, CopyToRange:=copyToRng, Unique:=False

Code I'm currently using (which works earlier in the code, just adding to different workbook tables)

Sub StockAvail(wb As Workbook, ws As Worksheet)

Dim SATemp As String
Dim SATempF As String
Dim wsSDC As Worksheet
Set wsSDC = MainWB.Worksheets(1)

SATemp = Dir(TemplPath & "\*Stock Availability*.xls*")
SATempF = TemplPath & "\" & SATemp

'Check if workbook open if not open it
If Not wbOpen(SATempF, wb) Then
    Set wb = Workbooks.Open(SATempF)
End If

'Call function add Vendor,Region, etc
Call VInfoAdd(wb, ws)


'Create Filter Criteria ranges
With MainWB.Worksheets.Add
    .Name = "FltrCrit"
    Dim FltrCrit As Worksheet
    Set FltrCrit = MainWB.Worksheets("FltrCrit")
End With

'Create Filter Criteria ranges
With FltrCrit
    Dim OOSdirectCrit As Range
    Dim myLastColumn As Long

    'Create OOS - Direct Filter Criteria Range
    .Cells(1, "A") = "Out of Stock - Direct"
    .Cells(2, "A") = "MS"
    .Cells(3, "A") = "=4"
    .Cells(2, "B") = "SOH"
    .Cells(3, "B") = "=0"
    .Cells(2, "C") = "Sup.Vend."
    .Cells(3, "C") = "<>MA05"
    .Cells(2, "D") = "Sup.Vend."
    .Cells(3, "D") = "<>MA07"
    .Cells(2, "E") = "Sup.Vend."
    .Cells(3, "E") = "<>MA09"
    .Cells(2, "F") = "Sup.Vend."
    .Cells(3, "F") = "<>MA11"


    'get last column, set range name
    With .Cells

        'find last column of data cell range
        myLastColumn = .Cells(2, Columns.Count).End(xlToLeft).Column

        'specify cell range
        Set OOSdirectCrit = .Range(.Cells(2, "A:A"), .Cells(3, myLastColumn))

    End With


'Copy Filtered data to specified tables
Dim tblFiltered As ListObject
Dim copyToRng As Range, SDCRange As Range

'OOS - Direct
'Store Filtered table in variable
Set tblFiltered = wb.Worksheets("Out of Stock (Direct Supply)").ListObjects("Table_Out_of_Stock_Direct")

'Remove Filtered table Filters
tblFiltered.AutoFilter.ShowAllData

'Set Copy to range on Filtered sheet table
Set copyToRng = tblFiltered.HeaderRowRange
Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range

'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=OOSdirectCrit, CopyToRange:=copyToRng, Unique:=False

The code is for 2 separate tables with different criteria, the first one to exclude DC codes and second one to include only DC codes. I only included the first one's code above, since it's the first issue (2nd one is the same but should be fixed if I can find the problem with the first one, since it's the same issue)

Any help will be greatly appreciated, I'm lost at where to look for problems with it at this point.

Simone Evans
  • 183
  • 2
  • 17
  • Does `copyToRange` include the relevant `Worksheet` and/or `Workbook` objects? – Ron Rosenfeld Apr 27 '20 at 19:08
  • @RonRosenfeld yes it does, in the code (all in the post), for all different workbooks and table, they are set. One example `Set tblFiltered = wb.Worksheets("Out of Stock (Direct Supply)").ListObjects("Table_Out_of_Stock_Direct") Set copyToRng = tblFiltered.HeaderRowRange Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range` – Simone Evans Apr 27 '20 at 19:17
  • There's a lot of code here and it would be quite an effort to replicate your scenario (without your actual data), so it would be good if you could boil it down to the minimum lines needed to reproduce the problem. Or if you can share a workbook which demonstrates the issue: even better. – Tim Williams Apr 27 '20 at 21:07
  • I looked back in my notes and the limit I found in VBA was maximum 2 filter criteria simultaneously. Anything more threw an error. There is also this post containing much useful information: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba – ionizing Apr 28 '20 at 11:28
  • @TimWilliams I removed some of the code from the post, hope this makes it easier to look at. If it's still not helpful I can create some short versions of the workbooks and share that. – Simone Evans Apr 28 '20 at 12:49
  • @ionizing, I looked at that post, but it's for normal autofilter, so don't think it's going to work in my case. Also, in other sections of the code, I use about 5 filter criteria, without any problems with Advanced filter... I thought it might be just that specific workbook that's corrupt so I created a new one, but still having the same issues with it. – Simone Evans Apr 28 '20 at 13:02

0 Answers0