Copy filtered columns to another sheet in macro

[geritz81]

Hi All,

I am currently working on a setup where I would like to filter 3 columns and copy the resultset to another sheet (insert below last row)

Sub STEP2_Copy_new_contracts_to_manual()
'copy contracts which are indicated as COPY TO MANUAL from tab POWERBI_IMPORT to Manual map AGR-Crop
    Dim shts As Worksheet, z As Integer, n As Integer
    Application.ScreenUpdating = False
    z = 1
    Dim expiration_date As Date
  expiration_date = Format(Worksheets("SCOPE").Range("E2").Value, "dd/MM/yyyy")
    Set shts = Sheets("POWERBI_IMPORT")
    
      Worksheets("POWERBI_IMPORT").UsedRange
            shts.UsedRange.AutoFilter Field:=19, Criteria1:="COPY TO MANUAL"
            shts.UsedRange.AutoFilter Field:=7, Criteria1:= _
        ">" & CLng(expiration_date), Operator:=xlAnd
            shts.UsedRange.AutoFilter Field:=9, Criteria1:="YES"
            shts.UsedRange.AutoFilter Field:=11, Criteria1:="Global"
            
            Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("A1").Offset(1), Sheets("POWERBI_IMPORT").Range("A1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("A" & Rows.Count).End(xlUp).Offset(1)
            Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("B1").Offset(1), Sheets("POWERBI_IMPORT").Range("B1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("B" & Rows.Count).End(xlUp).Offset(1)
            Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("D1").Offset(1), Sheets("POWERBI_IMPORT").Range("D1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("C" & Rows.Count).End(xlUp).Offset(1)
            Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("J1").Offset(1), Sheets("POWERBI_IMPORT").Range("J1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("D" & Rows.Count).End(xlUp).Offset(1)
            Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("H1").Offset(1), Sheets("POWERBI_IMPORT").Range("H1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("E" & Rows.Count).End(xlUp).Offset(1)
            shts.UsedRange.AutoFilter
    z = z + 1
   Worksheets("POWERBI_IMPORT").Activate
   ActiveSheet.Range("$A$1:$Z$10000").AutoFilter Field:=7, Criteria1:= _
        ">" & CLng(expiration_date), Operator:=xlAnd
   ActiveSheet.Range("$A$1:$Z$10000").AutoFilter Field:=9, Criteria1:="YES"
   ActiveSheet.Range("$A$1:$Z$10000").AutoFilter Field:=11, Criteria1:="Global"
    
    Application.ScreenUpdating = True

End Sub

This setup works perfect but I would like to extend the copy command to a paste values:

Sheets("POWERBI_IMPORT").Range(Sheets("POWERBI_IMPORT").Range("A1").Offset(1), Sheets("POWERBI_IMPORT").Range("A1").End(xlDown)).Copy _
                Sheets("Manual map AGR-Crop").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

I tried several options but they all through an "Compile Error: Expected:end of statement error message"

Anything I am missing ?

 

[Richard U]

your best bet is to record a macro of you doing it manually, then modifying the code to fit your purpose.