Function as Argument

Hi All,

I'm trying to write a macro that will take a function as an argument (see code below). Everything seems to be working except the function that gets called is applied to the macro-containing file and not the files it loops through. Any help would be greatly appreciated. Thanks in advance.

Regards,

Maksim

[vba]Function MacroF()

Range("A1:A20").Select
Selection.Copy
Range("F1").Select
ActiveSheet.Paste

End Function
Function MacroG()
Range("A1:A20").Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste
End Function

Function MacroH()
Range("A1:A20").Select
Selection.Copy
Range("H1").Select
ActiveSheet.Paste
End Function

Function FolderDialog()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a directory"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancelled"
Else
FolderDialog = .SelectedItems(1)
End If
End With
End Function
Sub File_Search(CodeToRun)

Dim Coll_Docs As New Collection
Dim Search_path, Search_Filter, Search_Fullname As String
Dim DocName As String
Dim wbk As Workbook

Dim i As Long

Search_path = FolderDialog() ' where ?
Search_Filter = "*." & InputBox("Enter the filetype to look for (e.g. xls)", "Identify File Type", "xls") ' what ?
Set Coll_Docs = Nothing

DocName = Dir(Search_path & "\" & Search_Filter)

Do Until DocName = "" ' build the collection
Coll_Docs.Add Item:=DocName
DocName = Dir
Loop

MsgBox "There were " & Coll_Docs.Count & " file(s) found."

For i = Coll_Docs.Count To 1 Step -1 '
Search_Fullname = Search_path & "\" & Coll_Docs(i)
Set wbk = Workbooks.Open(Search_path & "\" & Coll_Docs(i))
'(your code here)
wbk.Activate

With wbk.ActiveSheet
Application.Run (CodeToRun)
wbk.Close SaveChanges:=True
End With
Next

End Sub
Sub TestFSM()
Call File_Search(MacroF)
End Sub[/vba]

In my tests, it was the opened files that the macro worked on. You could be more explicit by saying Activeworkbook, but that is the default and so what is being used, and you activate each wb after opening it.

BTW, the argument in the TestFSM call needs quotes around it.

That's odd - I was banging my head against the keyboard all night and this morning after receiving your reply, everything is working. Thanks for your help!

M