I love you, Tom!! Thank you sooo much!!

"Tom Ogilvy" wrote in message
...
If it didn't avoid that workbook, then you would have gotten an error on
the
wkbk.Sheets("Timesheet").Range("A10:AE" & _
line, so it never would be to the msgbox line.

Did you get such an error.

Possibly above this line

wkbk.Sheets("Timesheet").Range("A10:AE" & _

put in
set sh = nothing
On Error Resume Next
set sh = wkbk.worksheets("TimeSheet")
On Error Resume Next
if not sh is nothing then

At the top do

dim sh as worksheet

This approach works.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...


Sub OpenFiles()
'Opens Files in Folder

Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Timesheets to Include in SAP PO Upload",
MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook
On Error Resume Next
set sh = wkbk.worksheets("TimeSheet")
On Error Resume Next
if not sh is nothing then


' With ActiveWorkbook.Sheets("Timesheet").UsedRange
' .Value = .Value
' End With

wkbk.Sheets("Timesheet").Range("A10:AE" & _
Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" & _
Consol.Range("E65536").End(xlUp).Offset(1,
0).Row).PasteSpecial
_
Paste:=xlPasteValues
Else
msgbox wkbk.Name & " has no timesheet"
End if
wkbk.Close
Next iFiles
End If

'Duplicate Test Here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi. Every week I run a timesheet procedure that opens all files within
a
folder, and copies a specific section from a specific sheet within each
file. Can I first run a procedure that makes sure that the sheet
called
"timesheet" exists in every file?

I can't just add an on error resume next block, becasue I need to
identify
WHICH files do not have that particular sheet. Any help would be
greatly
appreciated!

Cheers.

My code:
Sub OpenFiles()
'Opens Files in Folder

Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Timesheets to Include in SAP PO Upload",
MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
For iFiles = LBound(GetFiles) To UBound(GetFiles)
Workbooks.OpenText Filename:=GetFiles(iFiles)
Set wkbk = ActiveWorkbook


' With ActiveWorkbook.Sheets("Timesheet").UsedRange
' .Value = .Value
' End With

wkbk.Sheets("Timesheet").Range("A10:AE" &
Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" &
Consol.Range("E65536").End(xlUp).Offset(1, 0).Row).PasteSpecial
Paste:=xlPasteValues
wkbk.Close
Next iFiles
End If

'Duplicate Test Here

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub