Excel VBA code to check and add sheet

Hello experts,

I have an Excel workbook which has a single sheet named "Start"

I need a button here titled 'Run'. Clicking on it should run a macro code which will do the following:

If there already exists a sheet named "Scenario" in this workbook (which is not there when the macro first runs)
     Clear all the contents of the "Scenario" sheet
else
 Add a worksheet to the workbook with the name "Scenario"
End if


Thanks in advance!
j1981Asked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Hi, j1981
.

Edit: Apologies, button now added.

Please see attached. It handles hidden rows and columns and filters but there are other things that will cause problems. Can you give us a redacted copy of the sheet? The code is...
Option Explicit

Sub Reset_Scenario()

If Sheet_Exists("Scenario") Then
    Sheets("Scenario").Activate
    With Cells
        .EntireRow.Hidden = False
        .EntireColumn.Hidden = False
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
        .ClearContents
    End With
Else
    Sheets.Add
    ActiveSheet.Name = "Scenario"
End If

End Sub

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean

If xBook = "" Then xBook = ActiveWorkbook.Name

Sheet_Exists = False

On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next

End Function

Open in new window

Regards,
Brian.Scenario.xlsm
0
 
gowflowConnect With a Mentor Commented:
Is this what you want ?
make sure your macroes are activated and Run the Run button !!!
gowflow
Scenario.xls
0
 
j1981Author Commented:
Thank you both!
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
gowflowCommented:
I have no problem you attribute points un-equally but pls let me know why as your post clearly stated:
Clear all the contents of the "Scenario" sheet.
You did not mention anything about hidden rows or any of the like.
gowflow
0
 
j1981Author Commented:
Hi gowflow,

This question was to resolve one portion I didnt know from an existing code that I have. I didnt want to post the whole code here which is the reason why I raised a hypothetical question and I intended to copy paste the code from the solutions to my existing code to make it work.

Looking at both solutions, I felt that having the check as a function - as written by redmondb - was more readable and intuitive, rather than iterating through all of the worksheets(which works very well as well).

So I chose redmondb's code for my purpose and awarded him higher points, because it helped me to solve my bigger problem more easily.

But I want to thank you for your very quick response as well. Please let me know if that didnt explain.

 j1981
0
 
redmondbCommented:
Thanks,  j1981.
0
 
gowflowCommented:
no problem and your welcomed. For sure you can award point like you feel and for me the most important is to know that the solution worked for you. Your rightfully free to choose any code that you feel best integrate with your project and I thank you for your detailed explanation and rest assured my concern is to know that my solution works I knew it did as I test each and every solution I post prior to submitting.
Rgds/gowflow
0
 
j1981Author Commented:
Yes. I had tested it too and it worked perfectly. Once again, I really appreciate your response.

Thanks,

j1981
0
 
gowflowCommented:
No problem pls feel free to ask for help any time. Sorry for these comments if they were un-appropriate.
gowflow
0
 
j1981Author Commented:
They were not. Given the effort you put into helping out someone, I think it was only fair you asked.
j1981
0
 
gowflowCommented:
I will keep this question monitored so if in the future you need any help pls post a link to the question you need assistance and I will be glad to look at it (for sure if no one else speed me up !!!) :)
gowflow
0
 
j1981Author Commented:
:) I appreciate it.

j1981
0
 
gowflowCommented:
welcome
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.