Code run from within a sheet (not a module) only deals with itself unless
otherwise told. That is one of the reasons I like using objects when I create
books or sheets. Try this...

dim wks as worksheet

set wks = worksheets.add

with wks
wks.name = this_no
wks.range("A1").FormulaR1C1 = this_no
end with

Any time you need the new sheet just type wks and... Bob's your uncle...

HTH

"Jim at Eagle" wrote:

""" sheet2 is active at this pt """" then the following code

Sheets.Add After:=Sheets(3)
ActiveSheet.Name = this_no
Range("A1").FormulaR1C1 = this_no

Why would line 3 place "this_no" on sheet2
--
Jim at Eagle