Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new Sheet but not making it the Active Sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new Sheet but not making it the Active Sheet
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "David" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new Sheet but not making it the Active Sheet
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 wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new Sheet but not making it the Active Sheet
On Oct 16, 3:02 pm, Dave Peterson wrote:
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 wrote: 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. -- Dave Peterson 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new Sheet but not making it the Active Sheet
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" David wrote: On Oct 16, 3:02 pm, Dave Peterson wrote: 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 wrote: 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. -- Dave Peterson 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making for adding and naming a new sheet | Excel Discussion (Misc queries) | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
Copy my active sheet to a new sheet and open with an input form | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Adding Items To Active X ComboBox on Sheet | Excel Programming |