Directory Name - IF Named Range Exists

[lilanngel]

Hi All,

I need some serious help with this code! The goal is to loop through a group of files in a folder.. and have it return to me the names of files that contain a certain named range called "PullData". I have a loop macro that goes through the directory to pull file names... but I don't know how to make it first check for the named range. Please help! Also, does anyone know if this code will work in Excel 2003? I'm currently using 2007...

Sub CommandButton()

Application.ScreenUpdating = False

'Unhide Sheet11 (Formulas)
Sheet11.Visible = xlSheetVisible

Sheets("Formulas").Select
'PART 1: Loop File
'Turn off screen updating
'Application.ScreenUpdating = False

'File list
Dim Directory As String
Dim FileName As String
Dim IndexSheet As Worksheet
Dim row As Long
UserDir = Sheets("Formulas").Range("C2").Value

'Change the directory below as needed
Directory = UserDir
If Left(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

row = 11

Set IndexSheet = ThisWorkbook.ActiveSheet

FileName = Dir(Directory & "*.xls")
Do While FileName <> ""
IndexSheet.Cells(row, 2).Value = FileName
row = row + 1
FileName = Dir
Loop

Set IndexSheet = Nothing

'Go to MoveItems
MoveReplaceRenew

'Hide Sheet11
Sheet11.Visible = xlSheetVeryHidden
'Turn on screen updating
Application.ScreenUpdating = True



End Sub