I can't figure this out. The code all works as required however... when referance "Lalllo" is pasted into range "I32" the blanks it pastes are not actual blanks in terms of what sub Clearvar is doing I get error message 1004 no cells were found. If I then manually click on each blank cell and press 'Delete' then run the script, perfect, no problem. This does however kind of undermine what I was trying to achieve. Any help would be seriously appreacted.
Sub leftover() response = MsgBox("Are you sure you want to end this year!? this cannot be undone", vbYesNo) If response = vbNo Then MsgBox ("Operation Cancelled") Exit Sub End If Sheets("AnnualData").Select ActiveSheet.Unprotect Sheets("Loads").Select ActiveSheet.Unprotect Application.Goto Reference:="Lalllo" Selection.copy Sheets("AnnualData").Select Range("I32").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=False Call Clearvar Application.Goto Reference:="ADchangingdata" Selection.ClearContents Range("Havestyear").Value = Range("Harvestyear").Value + 1 ActiveSheet.Protect Range("D9").Select End Sub Sub Clearvar() 'On Error Resume Next Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = "" End Sub