-
I have the VBA code below. It should open the .xls files in the specified folder, copy a range, go to back to the workbook and so a special paste of the copied range then close the file and go on to the next .xls file in the folder.
I get a message that says "There is a large amount of data on the clipboard to you want to save it to paste later?" If I click yes or no the same thing occurs. The Visual Basic Editor pops up highlighting the paste special lines and this message comes up.
Runtimeerror '1004'
PasteSpecial method of Range class failed.
Can anyone help me get around this?
Sub OpenAndPasteAllFiles()
Dim sFile As String
Dim wrkBook As Workbook
Dim iCount%
Const sPath As String = "P:\Tlaaip\"
Set wrkBook = ActiveWorkbook
sFile = Dir(sPath & "*.xls")
Do While Len(sFile)
Workbooks.Open sPath & sFile
Range("A1:AI7").Select
Selection.Copy
ActiveWorkbook.Close
wrkBook.Activate
wrkBook.Sheets("data").Activate
Range("I3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I3").Select
wrkBook.Sheets("FrontPage").Activate
Range("A1").Select
sFile = Dir
Loop
End Sub
-
Try changing your PasteSpecial call to:
Code:
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
Good luck!
-
Nope Still getting the same errors.
Thanks for trying! Anyone else have any ideas?
