VBA Macro: Auto Filter -> Copy -> Paste to New Workbook -> Save

  • Hi guys,

    I followed this VBA code in youtube but I keep getting an error I am going crazy


    • Run-time error '1004':
      • Method 'ShowAllData' of object'_Worksheet' failed
    • Data does not copy to a new workbook
      • Does not automatically save

    Expected Outcome:

    • Filter unique values from the table (Cell F2 on Sheet 1)
    • Copy those values to a new workbook
    • Automatically save them with a customized file name
    • Summary: When running VBA, the value on Cell F2 should be filtered (and shown), copied, and pasted on a new workbook, then saved with a custom file name. After running the VBA, the filter should reset (show everything)

    Code Used:


    I attached the file where I used the code for reference

    Thank you!!

  • I think you only need to change the filter line to:

    raw.Range("A4").AutoFilter Field:=5, Criteria1:=region

    because Field:=5 is where to look for Criteria1:=region.

    Edited once, last by rollis13 ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!