Adding a new Sheet but not making it the Active Sheet

[David]

Hi

I want to add a new sheet to a workbook but not make it the active
sheet, is that possible?

I've written some code that loops through a worksheet, that is
referred to as the active sheet and then outputs the results onto
another, new sheet in the same workbook. I want to refer to the new
sheet by it's name and keep the active sheet as the active sheet.

Failing that how do you make a sheet active or not?

Thanks.

 

[Don Guillett]

There is another way I can't remember right now but try this.

Sub addshtwoactive()
goback = ActiveSheet.Name
MsgBox goback
Sheets.Add
Sheets(goback).Select
End Sub

 

[Dave Peterson]

You could just keep track

Dim ActWks as worksheet
dim NewWks as worksheet

set ActWks = activesheet
set newwks = worksheets.add
'go back to the sheet that was active
Actwks.select

But instead of refering to the new worksheet by its name, you could use the
newwks variable:

newwks.range("a1").value = "hi"
newwks.name = "newsheetnamehere"

 

[David]

You could just keep track

Dim ActWks as worksheet
dim NewWks as worksheet

set ActWks = activesheet
set newwks = worksheets.add
'go back to the sheet that was active
Actwks.select

But instead of refering to the new worksheet by its name, you could use the
newwks variable:

newwks.range("a1").value = "hi"
newwks.name = "newsheetnamehere"

Thanks but I either didn't explain properly or did something wrong as
it isn't working for me.

I added this to all functions:

Dim Sheet1 As Worksheet
Dim Sheet4 As Worksheet

I then added this to the function that opens the new file for
processing:

Set Sheet1 = ActiveSheet
Set Sheet4 = Worksheets.Add

'go back to the sheet that was active
Sheet1.Select

That all seems to be OK.

Then, I added this where the output needs to be processed:

Set Sheet4 = ActiveSheet

Sheet4.Select

Sheet4.Cells(1, 1).Value = "Results"

This is being output onto Sheet1, if I put
ActiveSheet.Cells(1,1).Value = "Results" it still puts it onto Sheet1.

What am I doing wrong? I'm using Excel '97 if that's the problem.

I could use an array to store the output in and then once everything
has been processed output it at the end but that would be quite slow I
suspect as there is a lot of data to be processed.

 

[Dave Peterson]

First, those variable names are (probably) the same as what excel used for your
code names.

So try replacing this:

Dim Sheet1 As Worksheet
Dim Sheet4 As Worksheet
with
Dim mySheet1 As Worksheet
Dim mySheet4 As Worksheet

But I don't think your code will work:

Set mySheet1 = ActiveSheet
Set mySheet4 = Worksheets.Add

mySheet1.Select

'this line will now have mySheet4 point to whatever sheet was active
'in this case, it points at the same sheet as mySheet1--the currently
'activesheet
Set mySheet4 = ActiveSheet

'this stays on the activesheet
mySheet4.Select

Sheet4.Cells(1, 1).Value = "Results"

============
I'd still use variable names that were a little more descriptive.

Dim ActWks as worksheet
dim NewWks as worksheet
set ActWks = activesheet
set newwks = worksheets.add
'go back to the sheet that was active
Actwks.select
'put something in the new worksheet
newwks.range("a1").value = "Results"