In XL2000, I use the following sub to update some stuff each month, but the
Replace portion doesn't replace anything :(

Cells B42 and G42 contain formulas that link to cells in 'Foodcost.xls'

No errors occur, but it's probably some simple syntax problem I can't
solve.

Any help?

Sub Renew()
Dim OldMonth As String
Dim NewMonth As String
Dim wkbk As Workbook
Set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
Workbooks.Open "Foodcost.xls"
wkbk.Activate
Range("Initial_Qty").Value = Range("On_Hand").Value
Range("Added_Used").ClearContents
OldMonth = Workbooks("Foodcost.xls").Sheets(5).Name 'i.e. Dec
NewMonth = Workbooks("Foodcost.xls").Sheets(6).Name 'i.e. Jan
Range("B42,G42").Cells.Replace What:=OldMonth, Replacement:=NewMonth,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Workbooks("Foodcost.xls").Close
Application.ScreenUpdating = True
End Sub

--
David

David,

Did you mean for this to be all of the columns from B to
G in Row 42?

Try "B42:G42" rather than "B42,G42"

-Donna

-----Original Message-----
In XL2000, I use the following sub to update some stuff
each month, but the
Replace portion doesn't replace anything :(

Cells B42 and G42 contain formulas that link to cells
in 'Foodcost.xls'

No errors occur, but it's probably some simple syntax
problem I can't
solve.

Any help?

Sub Renew()
Dim OldMonth As String
Dim NewMonth As String
Dim wkbk As Workbook
Set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
Workbooks.Open "Foodcost.xls"
wkbk.Activate
Range("Initial_Qty").Value = Range("On_Hand").Value
Range("Added_Used").ClearContents
OldMonth = Workbooks("Foodcost.xls").Sheets
(5).Name 'i.e. Dec
NewMonth = Workbooks("Foodcost.xls").Sheets
(6).Name 'i.e. Jan
Range("B42,G42").Cells.Replace What:=OldMonth,
Replacement:=NewMonth,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Workbooks("Foodcost.xls").Close
Application.ScreenUpdating = True
End Sub

--
David
.

Sharqua wrote

Did you mean for this to be all of the columns from B to
G in Row 42?

No. I only wanted to look at two cells (B42 and G42)... however:

Range("B42,G42").Cells.Replace What:=OldMonth, Replacement:=NewMonth,

Doh! Discovered I was referencing the wrong row! Changed to 41 and all is
well :) Forgot I had recently deleted a row after macro was written.

Thanks for jumping in, though.

--
David

Name the cells and refer to the name and that won't happen again

--
Don Guillett
SalesAid Software

"David" wrote in message
...
Sharqua wrote

Did you mean for this to be all of the columns from B to
G in Row 42?

No. I only wanted to look at two cells (B42 and G42)... however:

Range("B42,G42").Cells.Replace What:=OldMonth, Replacement:=NewMonth,

Doh! Discovered I was referencing the wrong row! Changed to 41 and all is
well :) Forgot I had recently deleted a row after macro was written.

Thanks for jumping in, though.

--
David

Don Guillett wrote

Name the cells and refer to the name and that won't happen again

Done. Thanks.

--
David