Bob,

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "B2:B102").Value = _
"'0000" & Workbooks("Book1.xls").Worksheets("Sheet1").Range( "B2:B102").Text

If you are satisfied always to act on the worksheet, you can use.

Range("B2:B102").Value = "'0000" & Range("B2:B102").Text

Bob

"Bob" wrote in message
. ..
I am trying to write a macro that will format column B for text and then
insert four zero's (0000) infront of an existing number in the cell
starting
at line 2 and continuing for 100 lines into that column of a spreadsheet.
The cells in the column are already populated with 8 digit numbers.
I'm a beginner at this and have been able to insert the zero's but it
takes
the number from the cell that I did the macro on and repopulates this
number
into other sheets. ( Am I making any sense?)
Would anyone have any ideas of guidance?
Many thanks
Bob