Manoj Gaidhankar
New Member
- Joined
- May 27, 2022
- Messages
- 20
- Office Version
- 2021
- Platform
- Windows
Hi there,
Please find the below code
Scenario1
I'm trying apply a filter on column C using Autofilter and then using the SpecialCells to copy only the visible cells which belongs to the column C. Further, pasting it in another workbook.
Scenario2
I'm trying apply a filter on column D using Autofilter and then using the SpecialCells to copy only the visible cells which belongs to the column D. Further, pasting it in another workbook.
Below is the table for your reference at the end.
Request you to please help me with this.
Thank you.
Please find the below code
Scenario1
I'm trying apply a filter on column C using Autofilter and then using the SpecialCells to copy only the visible cells which belongs to the column C. Further, pasting it in another workbook.
VBA Code:
Workbooks("Book3.xlsx").Worksheets(1).Range("A1").AutoFilter
Workbooks("Book3.xlsx").Worksheets(1). _
Range("A1").AutoFilter Field:=3, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>-"
With Workbooks("Book3.xlsx").Worksheets(1)
.Range("C2", .Range("C" & Rows.Count).End(3)). _
SpecialCells(xlCellTypeVisible).Copy '[B]Here the code copies the complete range and not the cell "C2" which is incorrect[/B]
End With
ActiveSheet. _
Range("H" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Scenario2
I'm trying apply a filter on column D using Autofilter and then using the SpecialCells to copy only the visible cells which belongs to the column D. Further, pasting it in another workbook.
VBA Code:
Workbooks("Book3.xlsx").Worksheets(2).Range("A1").AutoFilter
Workbooks("Book3.xlsx").Worksheets(2). _
Range("A1").AutoFilter Field:=4, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>-"
With Workbooks("Book3.xlsx").Worksheets(2)
.Range("D2", .Range("D" & Rows.Count).End(3)). _
SpecialCells(xlCellTypeVisible).Copy '[B]Here the code copies the only cell "D3" correctly[/B]
End With
ActiveSheet. _
Range("H" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Below is the table for your reference at the end.
Name1 | Name2 | Name3 | Name4 |
- | - | Nick Martin | - |
Nick | Nick | - | Nick |
- | Nick | - | - |
Nick Martin | Nick | - | |
Nick Martin | Nick Martin | - |
Request you to please help me with this.
Thank you.