#Value! error help

[hansjhamm]

I have a workbook with 2 sheets inside, one named Time in Store Summary
and the other Time in Store SummaryMTD.
B1:B5 contains date and hours information. This is where is gets odd.
If I view the Time in Store Summary while the code is running, I get
the correct information returned. But not for the Time in Store
SummaryMTD and vice versa...Viewing MTD, it is correct but the other
sheet gives me the '#value!'.
Below is the code that I was running (the only difference between the
the 2 is the "MTD")...Does anyone have a clue why this has happened?

Thanks in Advance!

Hans

Windows("R2 Time Report.xls").Activate
With Worksheets("Time In Store Summary")
With .Range("B1")
.Formula = "='Call Summary.xls'!$E$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B1")
Cells.Replace What:="From: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With

With Worksheets("Time In Store Summary")
With .Range("B2")
.Formula = "='Call Summary.xls'!$h$5"
.Value = .Value
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B2")
Cells.Replace What:="Through: ", Replacement:=""
Cells.Replace What:="Sunday, ", Replacement:=""
Cells.Replace What:="Monday, ", Replacement:=""
Cells.Replace What:="Tuesday, ", Replacement:=""
Cells.Replace What:="Wednesday, ", Replacement:=""
Cells.Replace What:="Thursday, ", Replacement:=""
Cells.Replace What:="Friday, ", Replacement:=""
Cells.Replace What:="Saturday, ", Replacement:=""
End With
End With



With Worksheets("Time In Store Summary")
With .Range("B3")
.Formula = "=NETWORKDAYS(B1,today(),'Time In Store
SummaryMTD'!M7:M15)"
.Value = .Value
With Worksheets("Time In Store Summary")
With .Range("B4")
.Formula = "=(B3*8)"
.Value = .Value
End With
End With
End With
End With
With Worksheets("Time In Store Summary")
With .Range("B5")
.Formula = "=NETWORKDAYS(TODAY()+1,B2,'Time In Store
SummaryMTD'!M7:M15)*8"
.Value = .Value
End With
End With

 

[Dave Peterson]

I'm not sure if this is your problem, but these sure looks funny to me:

..Formula = "='Call Summary.xls'!$E$5"
..Formula = "='Call Summary.xls'!$h$5"

Shouldn't you have a worksheet name in there someplace????

 

[hansjhamm]

Dave,

Thanks for the "catch" I inserted the workbook/sheet name, but still
have the issue. I decided to break it down into 2 different subs and it
works fine...If you have any additional ideas, it would be appreciated
as usual!

Thanks,


Hans