I have a macro adding a worksheet to a workbook. I am using:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

I then need to "get back" to the first sheet to make it active -
problem is I cannot reference it by its name - the name on the first
sheet will change each time I use this workbook.

Any help would be greatly appreciated

Thanks in advance.

sheets(1).select

" skrev:

I have a macro adding a worksheet to a workbook. I am using:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

I then need to "get back" to the first sheet to make it active -
problem is I cannot reference it by its name - the name on the first
sheet will change each time I use this workbook.

Any help would be greatly appreciated

Thanks in advance.

Maybe

Dim OldSht As Worksheet
Set OldSht = ActiveSheet ' or Sheets("Sheet1")
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
OldSht.Activate

VBA Noob


On 13 Dec, 21:29, wrote:
I have a macro adding a worksheet to a workbook. I am using:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

I then need to "get back" to the first sheet to make it active -
problem is I cannot reference it by its name - the name on the first
sheet will change each time I use this workbook.

Any help would be greatly appreciated

Thanks in advance.

On Dec 13, 4:36 pm, excelent
wrote:
sheets(1).select

" skrev:



I have a macro adding a worksheet to a workbook. I am using:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

I then need to "get back" to the first sheet to make it active -
problem is I cannot reference it by its name - the name on the first
sheet will change each time I use this workbook.

Any help would be greatly appreciated

Thanks in advance.- Hide quoted text -

- Show quoted text -

How would this work if it was different workbooks ? The sheet
examples worked great

Sub return_to()
Dim oldsheet As Worksheet
Set oldsheet = ActiveSheet
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
oldsheet.Select
End Sub


Gord Dibben MS Excel MVP

On Thu, 13 Dec 2007 13:29:48 -0800 (PST),
wrote:

I have a macro adding a worksheet to a workbook. I am using:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

I then need to "get back" to the first sheet to make it active -
problem is I cannot reference it by its name - the name on the first
sheet will change each time I use this workbook.

Any help would be greatly appreciated

Thanks in advance.