I have posted this question before but it was for sharepoint. I thought the error was due to sharepoint but now the file has been moved to a local folder yet it still happens sporadically. I am starting it as a new question hoping it will get answers.
The routine is to copy data from some child workbooks and paste to the parent file. The error happened on the line pastespecial. The error happens once in a while so I am wondering if anyone has had similar experience as I am not sure if it's the routine itself or an excel bug? Any help appreciated!!
Set mFSO = New FileSystemObject
mFolderPath = ThisWorkbook.Path
mFolderPath = Replace(mFolderPath, "/", "\")
mFolderPath = Replace(mFolderPath, "http:", "")
mFileName = Dir(mFolderPath & "\")
Set mWBD = ThisWorkbook
Set mWSD = s_Data
mFileName = Dir(mFolderPath & "\")
Set mWST = Worksheets.Add
lc = mWSD.Range("a3").End(xlToRight).Column
mWSD.Range("a3", mWSD.Range("a3").End(xlToRight)).Copy
mWST.Range("a3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
i = 0
mFileName = Dir(mFolderPath & "\")
Do
If InStr(mFileName, "_") <> 0 Then
i = i + 1
lrT = mWST.Cells(Rows.Count, "a").End(xlUp).Row
mFilePath = mFolderPath & "\" & mFileName
If bFileOpen(mFilePath) Then
bOpen = True
Else
bOpen = False
End If
If bOpen = False Then
Set mWBO = Workbooks.Open(mFilePath)
Set mWSO = mWBO.Worksheets("Data")
If mWSO.FilterMode Then
mWSO.ShowAllData
End If
lrO = mWSO.Cells(Rows.Count, "a").End(xlUp).Row
lcO = mWSO.Range("a3").End(xlToRight).Column
mWSO.Range("a4", mWSO.Cells(lrO, lcO)).Copy
With mWST
.Activate
.Range("a4").Select
If lrO > 3 Then
If lrT = 3 Then
.Range("a4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
.Cells(lrT + 1, "a").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End If
Application.CutCopyMode = False
mWBO.Close False
End With
End If
End If
mFileName = Dir()
Loop Until mFileName = ""
End With
Set mWBO = Nothing
Bookmarks