Solved

Excel VBA code to check and add sheet

Posted on 2013-01-09
13
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 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