Copy method of Worksheet class failed?

S

Steph

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
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check to see if sheet exists 4
Test if Sheet Exists - Tom Ogilvy 8
Import several txt files into separate sheets within 1 workbook 0
How do I combine these two? 1
Merge two pieces of code into one 3
Combine Code 2
Combine 2 macros 0
Copy method of Worksheet class failed 4

Top