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

LakshAvasthi

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
It looks like a syntax problem. Try changing this:

VBA Code:
Sheets(Sheet1).cell(1, 1).PasteSpecial xlPasteValues

to this:
VBA Code:
Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues
 

LakshAvasthi

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
It looks like a syntax problem. Try changing this:

VBA Code:
Sheets(Sheet1).cell(1, 1).PasteSpecial xlPasteValues

to this:
VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,174
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I suspect one of your ranges is invalid.

Replace this
VBA Code:
Range(Cells(13, j), Cells(lastrow, j)).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues

with this
VBA Code:
    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.
 
Solution

LakshAvasthi

New Member
Joined
Nov 16, 2021
Messages
3
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Thank you so much, it worked.
I have also changed this
VBA Code:
Sheets("Sheet1").Cells(1, 1).PasteSpecial xlPasteValues
to this
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,396
Messages
5,769,837
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top