Vba copy and paste filtered cells in new sheet

mark84

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,211
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
18
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
73,211
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
18
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
73,211
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
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,164,012
Messages
5,834,914
Members
430,326
Latest member
tomwax46

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