How to copy only the visible line of Autofilter and paste only values

[priisha]

Hi,
I've been trying to work with my excel data where I need to copy data from one sheet to another sheet.
In the source sheet I have the following data

Doc nr (A)	Date (B)	Name (C)	Nr (D)	Cost (E)	Amount TCY (E)	Currency (F)	Description (G)	Amount LCY (H)	Account2 (I)
5646546				31100
488967
456				31100

I want to copy the data from the source sheet only for the line where the Cost (E) is not equal to 31100 (meaning in this example only the row 1 and 3 should be copied). Also the columns are not copied in the same order but to different order in the target sheet. I have worked out a vba code that works however it also copy the formulas in the source cell which I dont want as I only want it to copy values

Sub createBFO

Dim ws1 As Worksheet, ws2 As Worksheet, lRow As Long
Set ws1 = ThisWorkbook.Sheets("KGH Ing Moms underlag")
Set ws2 = ThisWorkbook.Sheets("BFO ing moms")

'remove data
Worksheets("BFO ing moms").Range("A2:O200").ClearContents

lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

With ws1
    .Range("E1").AutoFilter Field:=1, Criteria1:="<>31100" 'add filter to exclude 31100
    .Range("B2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("A2")
    .Range("E2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("D2")
    .Range("G2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("H2")
    .Range("H2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("G2")
    .Range("I2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=ws2.Range("F2") 
    
    .Range("A1").AutoFilter
    
End With

End Sub

I've tried to change the code so the with ws 1 part become

With ws1
    .Range("E1").AutoFilter Field:=1, Criteria1:="<>31100" 'add filter to exclude 31100
    .Range("B2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("A2")
    .Range("E2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("D2")
    .Range("G2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("H2")
    .Range("H2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("G2")
    .Range("I2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy ws2.Range("F2").PasteSpecial xlPasteValues

But I got an error saying that code is incorrect.

Please help me!

 

[priisha]

just want to add that the error is

Compile error:
Expected: end of statement

 

[Fluff]

The pastespecial need to go on a separate line like

.Range("I2").Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Copy 
ws2.Range("F2").PasteSpecial xlPasteValues