Selecting Sheet or create new Sheet

[cornepiek]

Hey all you smart people

hoping someone can help me quick

i want a vba script that selects a sheet based on cell value (sheet name = cell value)

but, if the sheet does not exist, it must create it

quick and easy

 

[My Aswer Is This]

Assuming cell value will be Range("A1") on the active sheet.
Try this:

Sub Open_Sheet()
' Modified 11-28-17 7:00 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
ans = [A1].Value
Dim x As Long
x = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Name = ans Then Sheets(i).Activate: Exit Sub
    Next
        
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Application.ScreenUpdating = True
End Sub

 

[cornepiek]

that seems to be working, can you make it so that it looks as Sheet 1, cell A1

 

[My Aswer Is This]

Do you mean Sheet(1) or sheet named "Sheet1"

that seems to be working, can you make it so that it looks as Sheet 1, cell A1

 

[My Aswer Is This]

Try this:
Change sheet name to your needs:

Sub Open_Sheet()
' Modified 11-28-17 7:00 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
ans = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").[A1].Value
    For i = 1 To Sheets.Count
        If Sheets(i).Name = ans Then Sheets(i).Activate: Exit Sub
    Next
        
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Application.ScreenUpdating = True
End Sub

 

[cornepiek]

I tested your script

it works as expected, but theres a couple of tweaks required

first you turn off screen updating, that is fine if the sheet does not exist. if the sheet already exists, the "if" statement exits the Sub and does not reactivate screen updating

also, instead exiting the "Sub", can i activate another "sub" with "Application.Run "NextPart"

 

[My Aswer Is This]

What were are dealing with here now is called "Mission Creep"

You ask for me to get you a "Apple" and I get you a "Apple"
You ask for me to get you a "Orange" and I get you a "Orange"
You ask for me to get you a "Car" and I get you a "Car"


And then this can go on for ever:

In your original post you never gave the range to look in and you never gave the sheet name
Then in you second post you gave the sheet name
Now your asking for another script to run if?

also, instead exiting the "Sub", can i activate another "sub" with "Application.Run

And you did not say Application. Run what script.

And screen updating automatically turns back on when a script ends no matter if we tell it to or not.

 

[cornepiek]

I really do apologize for keep changing the goal post

i am not that adept at modifying VBA scripts, so i ask advise.
i am building something and ask assistance when i need excel to automatically do something and i dont know how.
when i implement whatever help was given. in most cases the help advise works perfectly, and i can adapt it to suit the next parts of what i need to achieve.

in this case, i am not able to modify the scrip to suit my needs, so i ask more advise on how to modify the requested script.


in this case, the fist scrip i requested sis not specify the sheet, because i was working on one sheet. but in creating a new sheet, i need the scrip to always check the correct sheet for the reference, and not jump back to the specific sheet. that i can do, but it feels messy. I tried to adapt the script to select the correct sheet, but my syntax keeps failing.

now i am faced with another issue and i cannot adapt the script myself. first, for some reason my screen-updating does not reactivate when the page already exists. i am not sure if it my installation thats buggy, but its the case. So i stepped through the script and found that is exits the sub and does not pass the "Application.ScreenUpdating = True".

secondly, i already tried changing the script to "Application.Run" instead of "exit Sub" before i asked.

i was prompted with the error in the screenshot,

so i simply asked you how do i run an application instead of exiting the sub

also, bu simply changing the "Exit Sub" to "Application.Run", won skip the "next where the sheet is created and renamed.