• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding New Worksheet

T

Tripp

Member
Hello,

I am trying to figure out how to add new worksheets up to a certain value but I am confused which method to use as a beginner VB'er.

In essence, I'm counting the number of worksheets then comparing that to a value of my choosing. If the number of worksheets is less, then I would like to add worksheets until they are the same value.

I keep creating infinite loops so any help would be greatly appreciated.

Regards,
Tripp
 
Hi ,

Try the following code in the ThisWorkbook section of your file :
Code: 
Public Sub AddSheets()
          Const SHEETCOUNT = 6
         
          Do While Me.Worksheets.Count < SHEETCOUNT
              Me.Worksheets.Add
          Loop
End Sub
Narayan
 
Thanks Narayan,

whats the "Me." for?

And would the "Const sheetcount" = x come after a dim sheetcount as integer?
 
Hi ,

Because I had mentioned that the code should be placed in the ThisWorkbook section , the keyword Me refers to the Workbook where this code is placed.

If the code had been placed in any Sheet section , the keyword Me would refer to the Worksheet where this code is placed.

You can get more details of this keyword here :

http://dailydoseofexcel.com/archives/2004/04/20/the-me-keyword/

As for your second doubt , the Const keyword is used to declare and assign a value to a constant ; this is diametrically opposite the Dim keyword , which is used to declare a VBA variable.

You can get more details of this keyword here :

https://www.wiseowl.co.uk/blog/s167/constants-vba.htm

Narayan
 
You must log in or register to reply here.
Back
Top