when you do

worksheets.Add

the sheet just created is the activesheet. If you will be changing another
sheet to the activesheet, just set a reference to this new sheet

Dim shNew as Worksheet
Dim shOld as Worksheet
set shOld = Activesheet
worksheets.Add
set shNew = Activesheet

Now shOld hold a referene to the original activesheet and shNew a reference
to the newly added sheet. (as an example)


--
Regards,
Tom Ogilvy


"Joe" wrote:

Hi

i have a set of data, which i will create a pivot table, and the pivot table
will be shown on a random new sheet number.

I am going to create a macro to refer the activesheet as the new sheet i
have just created. how do i do it? activesheet.next? it doesnt work ....

please help