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

"(E-Mail Removed)" 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, dennis.mccar...@us.atlascopco.com 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 <excel...@discussions.microsoft.com>
wrote:
> sheets(1).select
>
> "dennis.mccar...@us.atlascopco.com" 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), (E-Mail Removed)
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.