Filtered data copying from one workbook to another

[Manoj Gaidhankar]

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.

  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.

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.

 

[Manoj Gaidhankar]

I have already raised a concern on the below portal but there wasn't any update so raising a request here

Copy the Filtered data to another workbook

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. 810234

www.excelforum.com