-1

I'm trying to in Excel VBA create a macro that can take the data and filter them to a certain criteria. Then copy the filtered table into another spreadsheet. I've tried to do it by myself but have run into a roadblock.

The exact problem is shows me is Run time error"1004" where it says it cant be applied to a selected range? Which i dont understand what it means. The code error is shown to be at the filter part where the selection. Autofilter is.

I would greatly appreciate any tips, pointer or help to finish this VBA coding

The code i'm having problems with is the one below

Sub Filtering()

    Dim LastRow As Long

    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "PERSONA3"

    Sheets("PERSONA3").Activate

    'Find the last row
    LastRow = Range("A1").CurrentRegion.Rows.Count

    'Select Table
    Range("A1:FB" & LastRow).Select

    'Filter Table
    Selection.AutoFilter Field:=AR, Criteria1:="Cigarette"

    'copy paste
    Selection.Copy
      Sheets("PERSONA3").Range("A1" & LastRow).PasteSpecial x1PasteValues

    'Turn off Auto Filter
    Selection.AutoFilter

End Sub

The expected results for this program should be that another spreadsheet is created with the filtered table inputted into the new spreadsheet

Afam Yazid
  • 1
  • 1
  • 2
  • 2
    `x1PasteValues` - this is why you use `Option Explicit` – Tim Williams Feb 03 '19 at 17:44
  • You're trying to filter and copy the new blank worksheet that you just created. What is the name of the original source worksheet? –  Feb 03 '19 at 17:44
  • the original worksheets name is TOTAL the worksheet im copying from has over 100+ rows and columns. Also I cant use a set fix amount to copy and paste for each one as it has to be flexible with different amounts of row input – Afam Yazid Feb 03 '19 at 17:46
  • @TimWilliams What does option explicit do? – Afam Yazid Feb 03 '19 at 17:50
  • 1
    That's a basic Google question – Tim Williams Feb 03 '19 at 17:58
  • When you have an error you need to include two additional things in the problem description: 1) The exact and full error message; 2) The line of code where the error occurs. Without this information, not having access to the files used, it's difficult for people to know what the problem might be. Reading through all the lines of code or guessing is a waste of time for the people who might help. You need to make helping you as quick and easy as possible. You can use the [edit] link below the question to add the missing information. – Cindy Meister Feb 03 '19 at 18:31
  • What is `AR` in `Field:=AR`? I don't see that defined anywhere. – Comintern Feb 03 '19 at 20:30

1 Answers1

0

You are getting an error because you are attempting to apply an AutoFilter to the newly-created (and totally empty) worksheet.

You should apply the AutoFIlter to the previous sheet containing the data you wish to filter and then do the Copy/Paste

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • How do i specify which spreadsheet my autofilter will work on? – Afam Yazid Feb 03 '19 at 18:00
  • I've tried to use the With Worksheet to specify the table im referring to but a new problem arise where it says the sub or function is not defined (compile error) – Afam Yazid Feb 03 '19 at 18:10
  • [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [this](https://stackoverflow.com/questions/9628690/reference-excel-worksheet-by-name) could be useful – cybernetic.nomad Feb 03 '19 at 20:34