?
Solved

Excel VBA code to check and add sheet

Posted on 2013-01-09
13
Medium Priority
?
516 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 31

Assisted Solution

by:gowflow
gowflow earned 600 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

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 31

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 31

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 31

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 31

Expert Comment

by:gowflow
ID: 38766248
welcome
gowflow
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 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