paste filter results in new sheet or workbook with paste special not working.

[LakshAvasthi]

Hi All,
Greetings, I am new to this forum or any online forum to be precise. In case I do or say something wrong please bear with me.
I am new to excel and trying the below code to copy data after filtering to a new sheet(if you could tell me how to paste in the new workbook will be nice).
I checked the code till copy function with F8 it does copy the cell(copied cell is a single cell with value in number as -997.415) I want but for the part where I need to paste (PasteSpecial) it gives an error of type mismatch.
I have generated this and the rest of the code with great help from the various forums from MrExcel and internet surfing but couldn't figure this out. Any suggestions will be a great help.
And apologies for the long message.

Sub filterdata()
Dim r As Range
Dim lastrow As Long
Dim lastcol As Long
Dim s As String
Dim i As Integer, j As Integer

'Rename Sheets
ActiveSheet.Name = "Detailed"
Sheets.Add.Name = "Sheet1"
Sheets("Detailed").Activate
'sheet name to variable
s = "Detailed"

'count row & column
lastrow = Sheets(s).Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Sheets(s).Cells(12, Columns.Count).End(xlToLeft).Column

'Setting variable with column number from column name
Set r = Sheets(s).Range(Cells(12, 1), Cells(lastrow, lastcol))
i = Application.WorksheetFunction.Match("Article description", Range(Cells(12, 1), Cells(12, lastcol)), 0)
j = Application.WorksheetFunction.Match("Inventory Value", Range(Cells(12, 1), Cells(12, lastcol)), 0)

'Apply filter for Oil
r.AutoFilter Field:=i, Criteria1:="SHORTENING*"
r.AutoFilter Field:=j, _
                Criteria1:="<0", _
                Operator:=xlOr, _
                Criteria2:=">0"
Range(Cells(13, j), Cells(lastrow, j)).SpecialCells(xlCellTypeVisible).Copy
Sheets(Sheet1).cell(1, 1).PasteSpecial xlPasteValues

' Turn off filter
Sheets(s).AutoFilterMode = False

With Application
.CutCopyMode = False
End With
End Sub

 

[rlv01]

It looks like a syntax problem. Try changing this:

Sheets(Sheet1).cell(1, 1).PasteSpecial xlPasteValues

to this:

Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues

 

[LakshAvasthi]

It looks like a syntax problem. Try changing this:

Sheets(Sheet1).cell(1, 1).PasteSpecial xlPasteValues

to this:

Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues

Thank you for your response, This changed the error message to "Run time error 438: object doesn't support this property or method", on the same line. Any suggestions about that.

 

[rlv01]

I suspect one of your ranges is invalid.

Replace this

Range(Cells(13, j), Cells(lastrow, j)).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues

with this

    Dim Src As Range, Dest As Range
    
    Set Src = Range(Cells(13, j), Cells(lastrow, j)).SpecialCells(xlCellTypeVisible)
    Set Dest = Worksheets("Sheet1").Cells(1, 1)
        
    Src.Copy
    Dest.PasteSpecial xlPasteValues

and see where the error moves to.

 

[LakshAvasthi]

Thank you so much, it worked.
I have also changed this

Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues

to this

Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

In the previous code that also worked but I didn't understand the difference why Range worked but cell did not. But your code makes more sense looks cleaner and I can use I multiple times in my work now.
Thank you once again