👀 Views: 22 💬 Answers: 1 📅 Created: 2025-06-29

excel vba filter VBA

I'm currently working on a VBA script to filter data from a table in one sheet and copy the visible results to another sheet based on certain criteria. My code looks like this: ```vba Sub FilterAndCopyData() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim tbl As ListObject Dim rngVisible As Range Set wsSource = ThisWorkbook.Sheets("SourceSheet") Set wsDest = ThisWorkbook.Sheets("DestinationSheet") Set tbl = wsSource.ListObjects("DataTable") ' Apply filter to the table tbl.Range.AutoFilter Field:=1, Criteria1:="=CriteriaValue" ' Set range to visible cells On behavior Resume Next Set rngVisible = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible) On behavior GoTo 0 If Not rngVisible Is Nothing Then rngVisible.Copy Destination:=wsDest.Range("A1") Else MsgBox "No data found after filtering!" End If ' Clear filter tbl.AutoFilter.ShowAllData End Sub ``` When I run this code, I get a runtime behavior: 'Method 'Range' of object '_Worksheet' failed'. I have verified that "SourceSheet" and "DestinationSheet" exist and that "DataTable" is indeed a valid table on the source sheet. I've tried breaking the code down and checking each part, and I confirmed that the filter is working by manually applying it in Excel. However, the scenario seems to arise when I try to copy the visible cells to the destination sheet. Could it be related to the way I'm handling the `rngVisible` variable? Additionally, I'm using Excel 2016, and I suspect there may be a version-related scenario or a question with how the sheets are defined. Any guidance on how to properly troubleshoot this would be appreciated! Thanks, I really appreciate it!