Geek Answers Handbook

Filtering and copying Excel in VBA

I am working on a VBA script that pulls a date range from Access, then filters the data and creates a chart based on the filtered data. The filtered data will go to a separate sheet on which the chart will retrieve its data. I can get data from Access using an SQL statement, but my AutoFilter in Excel is wrong. Here is what I have ...

Sheet3.Range("F4:F500").AutoFilter(, "Riveter 01").Copy Destination:=Sheet2.Range("A5")

It gives a user-defined or object oriented error, and I cannot understand why. Is this correct or is there an easier way?

Thanks!

PS: This filter will be with 22 unique machines, so I planned to start a cycle for each machine. If this is not the fastest or the correct way, let me know.

+4
source share
2 answers

You need to break it into two parts. Filter and then copy / paste. See below:

 With Sheet3 .AutoFilterMode = False With .Range("F4:F500") .AutoFilter Field:=1, Criteria1:="Riveter 01" .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet2.Range("A5") End With End With

To remove a filter:

 On Error Resume Next Sheet3.ShowAllData On Error GoTo 0

The field "Error during restart" shows the following: if there is no filter to skip the error. Pay attention to the use of Sheet3 and Sheet2 for those who are looking for a common solution.

+10
source

I think you should do this in two different steps:

  • filter data
  • copy filtered data to another sheet

The answer here is a great example of how to do this: Macro autofilter, then copy only the visible data and paste in the next available line

0
source

More articles:


All Articles