I ran into this same error today. But i resolved the problem by going around it. All codes in my module worked fine for years until today! After investigating this problem i discovered that copying and pasting between two seperate instances of excel while the copying range have cell(s) within them that contains formula will yield this error. Therefore i took the formula containing range and pasted it into another column with just the values and then used the regular copy paste method. Problem solved. I'm not exactly sure why the error occured in the first place especially if it has been working for a long time! Here are my codes...

__OLD CODE:__

Public Sub loadlogs_Click()

Dim XLobject As Excel.Application, wbXLS As Excel.Workbook

rootpath = constant_information("rootpath")

Str_Dt = "D1": Stp_Dt = "D2"

D1 = Sheets("STL").Range(Str_Dt).Value: D2 = Sheets("STL").Range(Stp_Dt).Value

D_diff = DateDiff("m", D1, D2): If D_diff < 0 Then Exit Sub

screenfreeze (0) ': ThisWorkbook.Application.ScreenUpdating = False

firstrow = 4: lastcol = "X": RowIDCol = "AA"

lastrow1 = ThisWorkbook.Sheets("STL").Rows.Count

ThisWorkbook.Sheets("STL").Rows(firstrow & ":" & lastrow1).Delete Shift:=xlUp 'Selection.Delete Shift:=xlUp

Set XLobject = New Excel.Application: XLobject.Visible = False

For i = 0 To D_diff

targetdate = DateAdd("m", i, D1)

targetpath = rootpath & Year(targetdate) & " LogBooks\STL_" & Format(targetdate, "mmmyyyy") & ".xls"

'Debug.Print targetpath

'Dim RWstatus As Boolean

'RWstatus = Application.ThisWorkbook.ReadOnly

'If RWstatus = False Then screenfreeze (1): Exit Sub

'ThisWorkbookPath = ThisWorkbook.path & "\" & ThisWorkbook.Name

fs = chkfile(targetpath) '0=nofile;1=readwrite;2=readonly

If fs <> 0 Then 'And RWstatus = True Then

thiswrkbk_nxtrow = ThisWorkbook.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set wbXLS = XLobject.Workbooks.Open(targetpath, Password:="letmein", ReadOnly:=True) ', Writerespassword:="tracker")

lastrow2 = wbXLS.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row

'The next two lines work just fine! Copy range contains no formula.

wbXLS.Sheets("STL").Range("A" & firstrow & ":" & lastcol & lastrow2).Copy

ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, 1).Select: ActiveSheet.Paste

'The ActiveSheet.Paste below yields an errors because the range that is being copied have cells which contain formulas.

wbXLS.Sheets("STL").Range(RowIDCol & firstrow & ":" & RowIDCol & lastrow2).Copy

ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, col(RowIDCol)).Select: ActiveSheet.Paste

wbXLS.Application.CutCopyMode = False

wbXLS.Close Savechanges:=False

ElseIf fs = 0 Then

h = MsgBox("Fatal Error: Logbook '" & "STL_" & Format(targetdate, "mmmyyyy") & "' could not be located! Loading process for this book will be skipped. Please notify administrator!", vbExclamation)

End If

Next

XLobject.Quit

screenfreeze (1)

End Sub

__NEW CODE:__

Public Sub loadlogs_Click()

Dim XLobject As Excel.Application, wbXLS As Excel.Workbook

rootpath = constant_information("rootpath")

Str_Dt = "D1": Stp_Dt = "D2"

D1 = Sheets("STL").Range(Str_Dt).Value: D2 = Sheets("STL").Range(Stp_Dt).Value

D_diff = DateDiff("m", D1, D2): If D_diff < 0 Then Exit Sub

screenfreeze (0) ': ThisWorkbook.Application.ScreenUpdating = False

firstrow = 4: lastcol = "X": RowIDCol = "AA": CopyRowIDCol = "AB"

lastrow1 = ThisWorkbook.Sheets("STL").Rows.Count

ThisWorkbook.Sheets("STL").Rows(firstrow & ":" & lastrow1).Delete Shift:=xlUp 'Selection.Delete Shift:=xlUp

Set XLobject = New Excel.Application: XLobject.Visible = False

For i = 0 To D_diff

targetdate = DateAdd("m", i, D1)

targetpath = rootpath & Year(targetdate) & " LogBooks\STL_" & Format(targetdate, "mmmyyyy") & ".xls"

'Debug.Print targetpath

'Dim RWstatus As Boolean

'RWstatus = Application.ThisWorkbook.ReadOnly

'If RWstatus = False Then screenfreeze (1): Exit Sub

'ThisWorkbookPath = ThisWorkbook.path & "\" & ThisWorkbook.Name

fs = chkfile(targetpath) '0=nofile;1=readwrite;2=readonly

If fs <> 0 Then 'And RWstatus = True Then

thiswrkbk_nxtrow = ThisWorkbook.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set wbXLS = XLobject.Workbooks.Open(targetpath, Password:="letmein", ReadOnly:=True) ', Writerespassword:="tracker")

lastrow2 = wbXLS.Sheets("STL").Cells(Rows.Count, 1).End(xlUp).Row

wbXLS.Sheets("STL").Range("A" & firstrow & ":" & lastcol & lastrow2).Copy

ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, 1).Select: ActiveSheet.Paste

'The reason why i wrote the weird code in the 4 lines below is because a regular copy and paste _

like in the lines just above yields an error: runtime error '-2147427848 (80010108)'. after _

investigating this problem i discovered that copying and pasting between two seperate instances _

of excel while the copying range have cell(s) within them that contains formula will yield this _

error. Therefore in the following line of codes, i take the formula containing range and paste _

it into another column with just the values and then use the regular copy paste method.

**wbXLS.Sheets("STL").Range(RowIDCol & firstrow & ":" & RowIDCol & lastrow2).Copy**

**wbXLS.Sheets("STL").Range(CopyRowIDCol & firstrow).PasteSpecial Paste:=xlPasteValues**

**wbXLS.Sheets("STL").Range(CopyRowIDCol & firstrow & ":" & CopyRowIDCol & lastrow2).Copy**

**ThisWorkbook.Sheets("STL").Cells(thiswrkbk_nxtrow, col(RowIDCol)).Select: ActiveSheet.Paste**

wbXLS.Application.CutCopyMode = False

wbXLS.Close Savechanges:=False

ElseIf fs = 0 Then

h = MsgBox("Fatal Error: Logbook '" & "STL_" & Format(targetdate, "mmmyyyy") & "' could not be located! Loading process for this book will be skipped. Please notify administrator!", vbExclamation)

End If

Next

XLobject.Quit

screenfreeze (1)

End Sub