I still cannot figure this out and its become worse , im still a new to
this actually





"If ThisWorkbook.Sheets("shiftreport").Range("B541") <>
DestSheet.Range("B2").Value Then"

This is where I need to make a criteria  , if a value in cell ("B541")
did not the same with the value in "B2" then the macro should insert  the
copy range  instead of overriding the existing data  .



But if both have the same criteria value then it will continue updating
the range ( or by overriding the existing data )



Attached code  however copy  and paste  a value that tends to change to
#REF ( is it because the "copy range" is a link and formula data ??) ,



Set SourceRange = ThisWorkbook.Sheets("shiftreport").Range("A541:Q560")

From the above code , I set  a range from A541:Q560 , because sometimes
the data can be up to 20 rows , but not necessarily.

The range is actually a summary and coming from a link and formula





Below is the problems code :



Sub Copy_To_Another_Workbook()

Dim SourceRange As Range

Dim DestRange As Range

Dim DestWB As Workbook

Dim DestSh As Worksheet

Dim irow As Integer

Dim LastRow As Long





With Application

.ScreenUpdating = False

.EnableEvents = False



End With





If bIsBookOpen_RB("C:\Documents and
Settings\mohdnoor\Desktop\plannersperformance.xls") Then

Set DestWB = Workbooks("C:\Documents and
Settings\mohdnoor\Desktop\plannersperformance.xls")

Else

Set DestWB = Workbooks.Open("C:\Documents and
Settings\mohdnoor\Desktop\plannersperformance.xls")

End If







Set SourceRange =
ThisWorkbook.Sheets("shiftreport").Range("A541:Q560")

Set DestSheet = DestWB.Worksheets("PLANNER PRODUCTION")

Set DestRange = DestWB.Worksheets("PLANNER PRODUCTION").Range("A2")







If ThisWorkbook.Sheets("shiftreport").Range("B541") <>
DestSheet.Range("B2").Value Then

SourceRange.Copy

DestRange.Insert Shift:=xlDown



Else

SourceRange.Copy

Lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row



Set DestRange = DestSh.Range("A" & Lr + 1)





DestRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks:=yes, Transpose:=False



Application.CutCopyMode = False

DestWB.Close savechanges:=True









With Application

.ScreenUpdating = True

.EnableEvents = True





End With

End If







End Sub