When I run the following code it produces the correct result but keeps giving me a "mismatch (Error 13)" message. Any idea on what is going wrong and how I can modify my code?
Thanks,
Steve
Sub retrievebudget()
' Assign variables
TargetWB = ActiveWorkbook.Name
TargetWS = "copiedb"
cs = ActiveSheet.Name
LR = Sheets(cs).Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
Sheets(TargetWS).Columns("A:O").Clearcontents
For i = 8 To LR
'Start at row 8
fn = Sheets(cs).Range("C" & i).Value
sn = Sheets(cs).Range("D" & i).Value
rn = Sheets(cs).Range("E" & i).Value
'Check for Source workbook
If Not Dir(fn) <> "" Then
Sheets(cs).Range("F" & i).Value = Now()
Sheets(cs).Range("G" & i).Value = "No"
GoTo NextI
End If
'Open Source workbook
Workbooks.Open fn, Password:="", WriteResPassword:="", ReadOnly:=True
SourceWB = ActiveWorkbook.Name
'Assign Target Range
TLR = Workbooks(TargetWB).Sheets("copiedb").Range("A65536").End(xlUp).Row + 1
CheckRowNo = TLR
Set TargetRange = Workbooks(TargetWB).Sheets("copiedb").Range("A" & TLR)
'Copy and PasteSpecial
Sheets(sn).Range(rn).Copy
TargetRange.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
'Close Source workbook
Workbooks(SourceWB).Close SaveChanges:=False
'Close Workbook w/o Save
Sheets(cs).Range("F" & i).Value = Now()
TLR = Workbooks(TargetWB).Sheets("copiedb").Range("A65536").End(xlUp).Row + 1
If TLR > CheckRowNo Then
Sheets(cs).Range("G" & i).Value = "Yes"
Else
Sheets(cs).Range("G" & i).Value = "No"
End If
NextI:
Next i
Sheets(cs).Select
Application.ScreenUpdating = True
End Sub
Thanks,
Steve
Sub retrievebudget()
' Assign variables
TargetWB = ActiveWorkbook.Name
TargetWS = "copiedb"
cs = ActiveSheet.Name
LR = Sheets(cs).Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
Sheets(TargetWS).Columns("A:O").Clearcontents
For i = 8 To LR
'Start at row 8
fn = Sheets(cs).Range("C" & i).Value
sn = Sheets(cs).Range("D" & i).Value
rn = Sheets(cs).Range("E" & i).Value
'Check for Source workbook
If Not Dir(fn) <> "" Then
Sheets(cs).Range("F" & i).Value = Now()
Sheets(cs).Range("G" & i).Value = "No"
GoTo NextI
End If
'Open Source workbook
Workbooks.Open fn, Password:="", WriteResPassword:="", ReadOnly:=True
SourceWB = ActiveWorkbook.Name
'Assign Target Range
TLR = Workbooks(TargetWB).Sheets("copiedb").Range("A65536").End(xlUp).Row + 1
CheckRowNo = TLR
Set TargetRange = Workbooks(TargetWB).Sheets("copiedb").Range("A" & TLR)
'Copy and PasteSpecial
Sheets(sn).Range(rn).Copy
TargetRange.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
'Close Source workbook
Workbooks(SourceWB).Close SaveChanges:=False
'Close Workbook w/o Save
Sheets(cs).Range("F" & i).Value = Now()
TLR = Workbooks(TargetWB).Sheets("copiedb").Range("A65536").End(xlUp).Row + 1
If TLR > CheckRowNo Then
Sheets(cs).Range("G" & i).Value = "Yes"
Else
Sheets(cs).Range("G" & i).Value = "No"
End If
NextI:
Next i
Sheets(cs).Select
Application.ScreenUpdating = True
End Sub