Right now I am creating a worksheet in this manner:



When I do this it creates the sheet but immediately goes to it. Is there way to create it and not go to it?

in the meantime you could just put another statement after you add the worksheet that goes to the worksheet you want to be on

Why not just clicking on the tab of the worksheet you do want to have focus?

You can change your code to this:



Where "Sheet1" is the sheet you want to stay on.

Just wondering, but ThisWorkbook doesn't seem to work....is that something normal?

It doesn't sound normal, but I can't really answer that without you posting all your code please - to see what is going on - Maybe ThisWorkbook isn't the appropriate thing to use. What workbook object are you using?

I'm not sure I follow. I'm not really using any object I think.



This gives me an error.

Oh that's your entire code?

and what happens on that line - what error?

You're missing the Range part.

couldnt you just use worksheets("instructions").select

Bob, I think you already know my answer to that. It's my understanding from peers as well as experience that it's bad coding practice.

It will probably work in the short term/some term, but I wouldn't recommend using Select. It may work sometimes and cause a run time error at other times. Application.goTo will always work, as long as the cell you're going to isn't protected from selection.

but we arent talking about a cell here. simply making the worksheet the active worksheet again.

could use select, activate or anything close.

The code is what I posted above. It's being called from the main body with the command:



The error that it's returning is error 1004, Method of 'Goto' of object '_Application' failed

kruuth

So there is other code?

Why do you need to move to another worksheet after the new worksheet is created?

Don't you have a reference to the worksheet you were working on in the code?

Anyway, try this for the Application.Goto

That's the problem....I don't want to go to the worksheet. I want to just stay on the page that the button is on....using the code above, it jumps to the new page.

I think the code I posted will work, but you were using it missing the Range part.

Kruuth - If the code goes to the new sheet for a split second, then back to the place it was a split second later, is that a problem for you? That's what we're suggesting.

It's not possible to create a worksheet and for it not to become the active sheet.

Just as when you create a workbook it becomes the active workbook.

If you have code after you've created the new worksheet how can you access the button anyway?

Actually it occurs to me that since I'm going to be repeating the same few files I could just hide and unhide the sheets as I need them.

Is there a way to use clearcontents on an entire sheet?

Yes - you can use .Cells.ClearContents , where the initial dot is typed immediately AFTER whatever you are using for the worksheet object.

Example:

dim ws as Worksheet
set ws = thisworkbook.Worksheets("Sheet1")
ws.cells.clearcontents

Isn't that an entirely different question?.

I am with ipsors when he says that it is normally bad practice to use Select. If you are going to select something simply to use it or do something to it then I agree. But I am also with Bob G. If you want to BE somewhere, then select is perfectly OK.

So here is my code:

Yes, I know, I committed another cardinal sin: ActiveSheet. Since this macro is being activiated by a button, then the active sheet is the sheet on which the button resides. Unless you call it from some other piece of code, the only way you can click on the button is to have the page with the button active. Likewise I did not specify Sheets(MySheetName).Range("B1") because there is only one Cell B1 on the sheet with the button.

And yes, I sometimes say "ain't" and end sentences with prepositions.

I have had experiences where I want to go to a certain sheet on open event of workbook. Even there, selecting and activating worksheets can be funky. That's why I always use application.goto. Plus, with simply selecting a sheet, you don't know if you'll end up in range A5 or Z150000. Goto takes care of that. : )

True about selecting the sheet. Even if you select a specific cell, there is no assurance that you will actually see the cell. For example if you are currently viewing in the area of Z153 and you select A1, cell A1 will get the focus, but you might still be looking at Z153. It's a little bit like Word where you use the arrow key to navigate down and then decide to use the scroll bars or mouse. Then you press the arrow key again and wonder why you jump back 25 pages. You ain't where you thought you were.

However, in this case, you'll return to the last known location - somewhere in the vicinity of the button.

In general programming, I agree with being very specific. In this specific instance things are so canned that you can get away with cheating.

If you Select it, then I think that's correct. But Application.GoTo is different - especially with the Scroll argument correctly supplied. The Scroll argument according to my interpretation of the help files, is designed precisely to address what you're saying.

Regardless at the end of the day I pretty much agree with you guys. I just won't stop preaching against Select, because the time when I stopped using things like that was one of the best things I ever did, on a list of about 10 really great 'learning curves' in Excel vba.

my last comment.

there is always an exception.

To go back to the original question. The answer is no. You cannot create a new worksheet and not have it automatically be the active worksheet. You have to manually or by code go to the worksheet you want to be on.

How you do that is up to the individual.

Bob

I think that was mentioned earlier in the thread.

Thanks folks. I've got it working now by just having it jump back to the sheet I was working with. Much obliged.