AutoFilter - VBA Code - PROBLEM SOLVED

[paulyf]

Worked out the solution for myself (feeling very proud!!!!):

Sub Filter()
Dim arraycounter As Integer
Dim varData(4) As Variant
varData(0) = "St Pauls"
varData(1) = "St Lukes"
varData(2) = "St Matthews"
varData(3) = "Ansdell"
varData(4) = "Clifton County"
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
arraycounter = 0
For arraycounter = 0 To 4
Columns("A:A").Select
Selection.AutoFilter Field:=1, Criteria1:=varData(arraycounter)
Range("a2:" & Range("f65536").End(xlUp).Address).Copy
Dim ExcelSheet As Object
Set ExcelSheet = GetObject("C:PHFVB training" & varData(arraycounter) & ".xls")
Workbooks(varData(arraycounter) & ".xls").Sheets("Sheet1").Range("A2").PasteSpecial
Workbooks(varData(arraycounter) & ".xls").Close SaveChanges:=True
Workbooks(currentworkbook).Sheets(currentsheet).Activate
Next arraycounter
End Sub

I'm trying to write a macro to copy & paste filtered data into another workbook ("St Pauls". I have several problems (with this code!):

1) the filtered data is pasted into cell A1 of the "St Pauls" workbook, rather than A2;
2) when I open the "St Pauls" workbook to check the result, I have to unhide it from the "Window" menu; and
3) I want to repeat this exercise, using different filter criteria and different destination workbooks but fear (without your help!) I will have to copy the full code out for each change before ending the subroutine.

Any help would be greatly appreciated!

Cheers - Paul

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("A:A").Select
Selection.AutoFilter Field:=1, Criteria1:="St Pauls"
Range("a2:" & Range("f65536").End(xlUp).Address).Copy
Dim ExcelSheet As Object
Set ExcelSheet = GetObject("C:PHFVB trainingSt Pauls.xls")
Workbooks("St Pauls.xls").Sheet1.Range("A2").PasteSpecial
Workbooks("St Pauls.xls").Close SaveChanges:=True
Workbooks(currentworkbook).Sheets(currentsheet).Activate
End Sub

[ This Message was edited by: paulyf on 2002-05-22 01:46 ]

[paulyf]

Please ignore point 1 (being stupid). Points 2 and 3 remain.

Re. point 3 - would it be possible to create an array (of different filter criteria) and use a counter to work through them???

Cheers - Paul

[Jackeb]

why dont you create a string for the criteria and an interger for the field then have another sub which runs:

global c, f


sub call_autofilter()

c="St Pauls"
f=1
filter

End Sub

Sub Filter()
currentworkbook = ActiveWorkbook.Name
currentsheet = ActiveSheet.Name
Columns("A:A").Select
Selection.AutoFilter Field:=f, Criteria1:=c
Range("a2:" & Range("f65536").End(xlUp).Address).Copy
Dim ExcelSheet As Object
Set ExcelSheet = GetObject("C:PHFVB trainingSt Pauls.xls")
Workbooks("St Pauls.xls").Sheet1.Range("A2").PasteSpecial
Workbooks("St Pauls.xls").Close SaveChanges:=True
Workbooks(currentworkbook).Sheets(currentsheet).Activate
End Sub



I think this is what u were asking about... oitherwise i appologise!

Ed

[Jackeb]

use an array in a similar theme.... post if u can't work out how to set it up!

[paulyf]

Thanks for the reply Ed.

I don't mean to be hard of thinking but would appreciate it if you could show me how to set the array up. Ideally I'd like to be able to have, say 5, different filter criteria. Each time the criterion changes, the results would be pasted to a different workbook. Would it also be possible to link the different criteria to different workbooks in the array???

Hope the above makes sense and is not too much bother.

Much obliged to you.

Cheers - Paul