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

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

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!!

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!!

macro to autofilter other file and copy in opened file
 

Thankx mate, it is perfect!!