Copy method of Worksheet class failed?
 

I have a procedure that opens 60 files and copied 1 sheet from each
file into a single workbook to consolidate the data. The procedure
runs great until I get the the 35th file, and then I get an error:
Copy method of Worksheet class failed. The debugger highlights the
line of code below that I have in front of:

Sub OpenFiles()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Budget Templates to Include in SAP 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

wkbk.Sheets("Opex & CAPEX").Copy
After:=ThisWorkbook.Worksheets(3)
With ActiveSheet.UsedRange
.Value = .Value
End With
wkbk.Close
Next iFiles
End If

End Sub

Any ideas on how to fix this?? Thanks in advance!!

-Steph

Copy method of Worksheet class failed?
 

It might be the problem described he

http://support.microsoft.com/default...34&Product=xlw

--
Jim Rech
Excel MVP
"Steph" wrote in message
om...
I have a procedure that opens 60 files and copied 1 sheet from each
file into a single workbook to consolidate the data. The procedure
runs great until I get the the 35th file, and then I get an error:
Copy method of Worksheet class failed. The debugger highlights the
line of code below that I have in front of:

Sub OpenFiles()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim wkbk As Workbook

GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.*),*.*", _
Title:="Select Budget Templates to Include in SAP 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

wkbk.Sheets("Opex & CAPEX").Copy
After:=ThisWorkbook.Worksheets(3)
With ActiveSheet.UsedRange
.Value = .Value
End With
wkbk.Close
Next iFiles
End If

End Sub

Any ideas on how to fix this?? Thanks in advance!!

-Steph