Search & move sheet

[DanK]

So far in my many variations i can't get this to work so maybe the guru's here can pinpoint my obvious flaw in logic. This is an on open script that is supposed to scan the sheets within another workbook and skip a specific named sheet then add a formula to see if the date in the cell is greater then a year old (problem with the cell being blank returns true). If so then moves the scanned sheet into the starting workbook and then deletes the scanned sheet. [Summary: checks sheets to see if it is old then moves into another workbook and removes the original]

Thanx for any wisdom you can share.

' This code is used from one workbook "Archives 1.0.xls" on open and searches another workbooks sheets
Dim SlotArchive
Archive = Workbooks("Cabinet ID 1.0.xls").Worksheets.Count
For i = 1 to Archive
' Looks for the sheet name and skips if "Index"
if Sheets(i).Name <> "Index" then
' Unlocks the sheet
ActiveSheets.Unprotect Password = (Workbooks("Slot Array 3.5.xls").Worksheets("Intro").Range("U2")

' Selects cell S1 and places a formula to calculate if the number of days from the date in cell C4 to today exceeds 1 year
Sheets(i).Range("S1").Select
Selection.Clearcontents
Range("S1").Formula = "=Days360(C4,Today())"

' if the days are longer then 1 year, problem is if the cell is blank it always answers TRUE
and if Sheets(i).Range("S1").Value > "365" then

' Clears the previous formula
Range("S1").Select
Selection.ClearContents

' Copies the sheet and moves it to another workbook
ActiveWorkbook.Sheets(i).Copy
ActiveSheet.Move After:=Workbook("Archives 1.0.xls").Sheets("Index")

' Delets the old sheet from the previous workbook
Application.DisplayAlerts = FALSE
Workbooks("Cabinet ID 1.0.xls").Worksheets(i).Delete
Application.DisplayAlerts = TRUE
end if
end if
next i

 

[VoG]

All that selecting is unnecessary and inefficient. I think that you need to remove the quotes

If Sheets(i).Range("S1").Value > 365 Then

Please use code tags when posting code.

 

[DanK]

The quotes will be omitted later, they just help me keep track of where i am.

 

[VoG]

Sorry, that makes no sense. If you are checking for a numeric value you do not need ". In fact, that will give erroneous results since a value in quotes is always greater than a numeric value.

 

[DanK]

ok i see what you are saying, that makes sense. The other problem is that if the value it is checking against "Days360(C4,today())" if C4 is blank it gives the value from 1/1/1900 so in the 48000 range. I really didn't want to add extra if statements to check for blanks.

 

[VoG]

There is no alternative to checking for blanks I'm afraid.