Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Autofiltered rows to another workbook using macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Autofiltered rows to another workbook using macro
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Autofiltered rows to another workbook using macro
On Jul 27, 1:18 pm, Mike H wrote:
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" wrote: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Copy From One Workbook To Another | Excel Discussion (Misc queries) | |||
Copy Results from Autofiltered List | Excel Worksheet Functions | |||
In Macro How can i Change Autofiltered data one by one | Excel Discussion (Misc queries) | |||
copy rows to another workbook | Excel Discussion (Misc queries) | |||
How to plot only visible autofiltered rows in a data list | Charts and Charting in Excel |