Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to autofilter other file and copy in opened file
Yo,
say i'm in a certain workbook: book1. from there I want a macro to autofilter another file (that is not yet opened) (e.g. c:\other file) based on the value a1 of sheet1 of book1. once the other file is filtered, the filtered range has to be copied to sheet2 of book1. is this possible? thankx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to autofilter other file and copy in opened file
Sub Macro1()
Dim File1 As Workbook Dim File2 As Workbook Set File1 = ActiveWorkbook Set File2 = Workbooks.Open(Filename:= _ "C:\Other File.xls") Range("A1", Range("A1").SpecialCells(xlLastCell)).AutoFilter Range("A1", Range("A1").SpecialCells(xlLastCell)).AutoFilter _ Field:=1, Criteria1:=File1.Sheets(1).Range("A1") Range("A1").CurrentRegion.SpecialCells(xlCellTypeV isible) _ .Copy File1.Sheets(2).Range("A1") File2.Close False End Sub HTH Charles Chickering matthias wrote: Yo, say i'm in a certain workbook: book1. from there I want a macro to autofilter another file (that is not yet opened) (e.g. c:\other file) based on the value a1 of sheet1 of book1. once the other file is filtered, the filtered range has to be copied to sheet2 of book1. is this possible? thankx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to autofilter other file and copy in opened file
thankx it works
but how can i make sure that the data is copied with the existing formats (bold, italic) and also with the original columnwidth? thankx this is super!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to autofilter other file and copy in opened file
I ran it with formatting applied and it stayed. The column width is a
little tricky. try this sub: Sub Macro1() Dim File1 As Workbook Dim File2 As Workbook Dim afRange As Range 'AutoFilterRange Dim clm As Range 'Column Set File1 = ActiveWorkbook Set File2 = Workbooks.Open(Filename:= _ "C:\Other File.xls") Range("A1", Range("A1").SpecialCells(xlLastCell)).AutoFilter Range("A1", Range("A1").SpecialCells(xlLastCell)).AutoFilter _ Field:=1, Criteria1:=File1.Sheets(1).Range("A1") Set afRange = Range("A1").CurrentRegion afRange.SpecialCells(xlCellTypeVisible) _ .Copy File1.Sheets(2).Range("A1") For Each clm In afRange.Columns File1.Sheets(2).Cells(1, clm.Column).ColumnWidth = clm.ColumnWidth Next File2.Close False End Sub Charles matthias wrote: thankx it works but how can i make sure that the data is copied with the existing formats (bold, italic) and also with the original columnwidth? thankx this is super!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to autofilter other file and copy in opened file
Thankx mate, it is perfect!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to run a macro automatically when the file is opened? | Excel Discussion (Misc queries) | |||
How to auto activate macro when file opened | Excel Worksheet Functions | |||
Close file and run macro from newly opened file | Excel Programming | |||
How to make macro start when file is opened | Excel Programming | |||
Copy cells from xls file opened in browser | Excel Programming |