Vba copy and paste filtered cells in new sheet

mark84

New Member
Joined
Jan 22, 2021
Messages
5
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: 26
  • Nuovo lst.PNG
    Nuovo lst.PNG
    71.7 KB · Views: 24

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,446
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Whereabouts in the output sheet should the data get pasted?
Also do you just want values, or everything?
 

mark84

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,446
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

mark84

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Perfect!! It works well.
You ve been very gentle!
Thank you very much

Have a nice day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,446
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,487
Members
416,031
Latest member
Omega67

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