Solved

Excel VBA code to check and add sheet

Posted on 2013-01-09
13
483 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

15 Experts available now in Live!

Get 1:1 Help Now