Solved

Excel VBA code to check and add sheet

Posted on 2013-01-09
13
472 Views
Last Modified: 2013-01-11
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!
0
Comment
Question by:j1981
  • 6
  • 5
  • 2
13 Comments
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 150 total points
ID: 38761105
Is this what you want ?
make sure your macroes are activated and Run the Run button !!!
gowflow
Scenario.xls
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 38761128
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
 

Author Closing Comment

by:j1981
ID: 38761549
Thank you both!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38762074
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
 

Author Comment

by:j1981
ID: 38763240
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38763366
Thanks,  j1981.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 29

Expert Comment

by:gowflow
ID: 38764075
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
 

Author Comment

by:j1981
ID: 38764112
Yes. I had tested it too and it worked perfectly. Once again, I really appreciate your response.

Thanks,

j1981
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38764286
No problem pls feel free to ask for help any time. Sorry for these comments if they were un-appropriate.
gowflow
0
 

Author Comment

by:j1981
ID: 38764388
They were not. Given the effort you put into helping out someone, I think it was only fair you asked.
j1981
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38764417
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
 

Author Comment

by:j1981
ID: 38764432
:) I appreciate it.

j1981
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38766248
welcome
gowflow
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now