none
How to add sheet,and name it using loop RRS feed

  • Question

  • Hello All,

    I would like to create a sheet and name it in pre defined name with a counter.

    Fisrt sheet should be should be version 1 Second will be version two and so on...

    I succeded creating a shhet with the name i wanted but couldnt handel the loop issue...

    Thanks alot on your help

    Amir 

     

    Sunday, July 24, 2011 1:00 PM

All replies

  • Amir,

    Sub AddSheets()
    Dim i As Integer
    For i = 1 To 2
    Sheets.Add.Name = "Sheet Version " & i
    Next i
    End Sub


    HTH, Bernie
    Sunday, July 24, 2011 3:07 PM
  • hi,
     
    another possibility,
     Sub AddSheets()
    Dim i As Integer
    For i = 1 To 2
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Ver" & i
    Next i
    End Sub
     
    --
    isabelle
     
     
    Monday, July 25, 2011 12:46 AM
  • Thanks,It helped.

    One more thing.Each time the procedure is runnig it opens 2 sheets in one time

    How can i make ut to open each time one sheet?

    Monday, July 25, 2011 7:12 AM
  • The

    for i = 1 to 2

    means that it creates 2 sheets. You could change it to this to control the number of sheets:

    Sub AddSheets2()
    Dim i As Integer
    For i = 1 To Application.InputBox("Ending number?")
    Sheets.Add.Name = "Sheet Version " & i
    Next i
    End Sub

    Or this, to control the starting number and final number of sheets - for example, you want to add sheet 6 to 9...

    Sub AddSheets3()
    Dim i As Integer
    For i = Application.InputBox("Starting number?") To Application.InputBox("Ending number?")
    Sheets.Add.Name = "Sheet Version " & i
    Next i
    End Sub


    HTH, Bernie
    Monday, July 25, 2011 1:14 PM
  • For this code it creates number of sheets with defined name all at once

    But what if i want it to create 1 sheet and name it "Version 1" and then exit the sub.

    and in the second time i'll decide to run it it will create second sheet with the name "Version 2"?

    I want it to create one at a time and exit the sub each time and not all sheets togather..

    Thanks alot for yuour help:)))

    Wednesday, July 27, 2011 12:50 PM
  • Do it this way:

    Sub AddSheets4()
    
    Dim i As Integer
    
    Dim myStr As String
    
    Dim myName As String
    
    
    
    myName = "Version "
    
    On Error GoTo MakeSheet:
    
    
    
    For i = 1 To Worksheets.Count + 1
    
    myStr = Worksheets(myName & i).Name
    
    Next i
    
    Exit Sub
    
    MakeSheet:
    
    Sheets.Add.Name = myName & i
    
    End Sub
    
    

     


    HTH, Bernie

    Wednesday, July 27, 2011 1:33 PM