macro to autofilter other file and copy in opened file

[matthias]

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

 

[Die_Another_Day]

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(xlCellTypeVisible) _
.Copy File1.Sheets(2).Range("A1")
File2.Close False
End Sub

HTH

Charles Chickering

 

[matthias]

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

 

[Die_Another_Day]

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]

Thankx mate, it is perfect!!