Copy Autofiltered rows to another workbook using macro

[ashish128]

Hi All,

I was trying to write a macro code to autocopy autofiltered rows from
one work book to another but couldn't figure it out.

Well, Here are the details

File abc.xls (Closed)
File xyz.xls (Closed)

Open abc.xls (Sheet name : aaa)
autofilter on criteria (I have that criteria so we can skip this
part)
Copy the cells from resulting rows (Range : Column "A" to "V")

Open xyz.xls (Sheet name : xxx)
paste the copied cells beneath last (filled) row. (I mean append)

Close abc.xls (Without Saving)
Close xyz.xls (after Saving)

Any help is welcome.

Thanks & Regards
Ashish Sharma

 

[Mike H]

Hi,

You didn't say where you wanted the data pasting into the second workbook so
this look for the last used cell in Column A of sheet BBB

Sub mariner()
Application.DisplayAlerts = False
Workbooks.Open Filename:="c:\abc.xls"
Sheets("AAA").Select
Range("A:V").Select
'Apply Autofilter
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveWorkbook.Close savechanges:=False
Workbooks.Open Filename:="c:\xyz.xls"
Sheets("BBB").Select
Range("A65536").End(xlUp).Select
ActiveSheet.Paste
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

Mike

 

[ashish128]

Hi,

You didn't say where you wanted the data pasting into the second workbook so
this look for the last used cell in Column A of sheet BBB

Sub mariner()
Application.DisplayAlerts = False
Workbooks.Open Filename:="c:\abc.xls"
Sheets("AAA").Select
Range("A:V").Select
'Apply Autofilter
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveWorkbook.Close savechanges:=False
Workbooks.Open Filename:="c:\xyz.xls"
Sheets("BBB").Select
Range("A65536").End(xlUp).Select
ActiveSheet.Paste
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
End Sub

Mike













- Show quoted text -

Thanks Mike. But your code is doing two unwanted things

1. Range("A65536").End(xlUp).Select
ActiveSheet.Paste
This code will place the cursor on the last filled row (i.e.
on row which has data) and therefore the paste command overwrites the
contents of this row. It should place the cursor below this row.

2. Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
This Code also copies the row above the result i.e. the row
with headings.

Kindly tell if I am doing something wrong

Thanks & Regards