VBA Mismatch (Error 13) - Help needed

[sbooth]

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

 

[VoG]

Which line is highlighted when you press the Debug button?

 

[sbooth]

It does not give me the option to debug it. I'm trying to figure out how to debug it from within VBA editor.

Steve

 

[sbooth]

I fixed the range in line 6 of the code and now it works fine. Thank you


Sub retrievebudget()
' Assign variables
TargetWB = ActiveWorkbook.Name
TargetWS = "copiedb"
cs = ActiveSheet.Name
LR = Sheets(cs).Range("B65536").End(xlUp).Row 'This range was wrong
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