Vba copy and paste filtered cells in new sheet

mark84

New Member
Joined
Jan 22, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I need your help.
I want to copy in a new sheet the filtered data of two sheets.
In the sheet "List AS IS" I want to filter the data of the column "W" for the criteria "Rimuovere" and then copy and paste the filtered data from "X" to "AM" in the sheet "Output"
In the sheet "Nuovo lst" I want to filter the data of the column "D" for the criteria "variare" and then copy and past the filtered data from "E" to "T" below the cells pasted before in the sheet "Output".
Since my data will not always be the same, I d want that the filter could consider dynamic parameter and not absolute parameter like a static cell.

This is my VBA but it doesnt't work well.



Could you help me please?
Thanks



Sub clienti_marginali()
'
' clienti_marginali Macro

'
Sheets("List AS IS").Select
Range("W1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AM$736").AutoFilter Field:=23, Criteria1:="<>"
Sheets("Nuovo lst").Select
Range("D1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$T$748").AutoFilter Field:=4, Criteria1:="<>"
Windows("estrapolazione dati da caricare Gen21 (150025) - C.xlsm").Activate
Sheets("List AS IS").Select
Range("X" & Rows.Count).End(xlUp).Select
Selection.Copy
Sheets("Output").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A24").Select
Selection.End(xlDown).Select
Range("A1187").Select
Windows("estrapolazione dati da caricare Gen21 (150025) - C.xlsm").Activate
Sheets("Nuovo lst").Select
Range("E" & Rows.Count).End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Output").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1153").Select
ActiveWindow.SmallScroll Down:=-12
Application.CutCopyMode = False

End Sub
 

Attachments

  • As Is.PNG
    As Is.PNG
    65.6 KB · Views: 40
  • Nuovo lst.PNG
    Nuovo lst.PNG
    71.7 KB · Views: 36

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi & welcome to MrExcel.
Whereabouts in the output sheet should the data get pasted?
Also do you just want values, or everything?
 
Upvote 0
Thanks for reply.
The data filtered in the sheet AS IS should be pasted from A1 in the output sheet.
The data filtered in the sheet Nuovo lst should be pasted below the last full raw in the output sheet.

I just want values
 
Upvote 0
Ok, how about
VBA Code:
Sub mark()
   With Sheets("List As Is")
      .Range("A1:AM1").AutoFilter 23, "Rimuovere"
      .AutoFilter.Range.Offset(1).Columns("X:AM").Copy
      Sheets("Output").Range("A1").PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
   With Sheets("Nuovo lst")
      .Range("A1:T1").AutoFilter 4, "variare"
      .AutoFilter.Range.Offset(1).Columns("E:T").Copy
      Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Solution
Perfect!! It works well.
You ve been very gentle!
Thank you very much

Have a nice day
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top