spreadsheet name's number increment

[somenoob]

hi everyone, i have the following codes..

Set wks = Worksheets.Add(After:=Worksheets(Worksheets.Count - 1))
wks.Name = "Chk_"

how do i code it so that 'Chk_' will display Chk_1 when i click a button to run the macro. then Chk_2 when i click again and Chk_3 and so on..

in my excel, there are the following spreadsheets:
list, chk_1, chk_2, chk_3, chk_,4, chk_,5, chk_6, SQL Used.

so when i click the button with my macro, it should be Chk_7
but if i delete two spreadsheet, chk_6 and chk_7. Then i click the button, it should give me chk_6

 

[villy]

You can try this:

Sub newsheet()
For i = 1 To Sheets.Count
    nem = "Chk_" & i
    On Error Resume Next
    If Sheets(nem) Is Nothing Then
        Sheets.Add after:=Sheets(i - 1)
        Sheets(i).Name = nem
    End If
Next
End Sub

 

[somenoob]

Hi thanks for the reply. And the codes work. but why does it create two spreadsheet?

Can you help me edit the codes? i would like to create 1 spreadsheet when i run the macro. Thanks

 

[somenoob]

i also noticed that when i click the macro, it is in between chk_5 and chk_6. not between the chk_6 and SQL used.

And the spreadsheet created as Chk_7 and Chk_8. for Chk_8, the values i want to put in is there

Sheets(nem).Range("A1") = "Failure_Class"
Sheets(nem).Range("A3") = "Item Category"
Sheets(nem).Range("A4") = "Actual"

but for Chk_7 it is empty

 

[villy]

how many sheets you have initially?

 

[villy]

I checked it works with initially with sheets as follows:
Chk_1,
Chk_2,
Chk_3,
Chk_4,
Chk_5,
SQL
then when I run the macro it adds Chk_6 between Chk_5 and SQL
then when you run again the macro it adds Chk_7 between Chk_6 and SQL and so on...
I am expecting it run the same way in yours
Feedback please..
Thanks

 

[somenoob]

at the start, i have the following spreadsheets (8 spreadsheets)

in order: 'List' , 'Chk_1' , 'Chk_2' , 'Chk_3' , 'Chk_4' , 'Chk_5' , 'Chk_6' , 'SQL used'.

when i run the macros with the codes you provided, it creates two spreadsheet (Chk_7 and Chk_8) in between of Chk_5 and Chk_6. i would like it to be positioned between Chk_6 and SQL used. Also i would like to create only one spreadsheet at a time when i run the macro. As in instead of creating Chk_7 and Chk_8, i would like to create Chk_7 in between of Chk_6 and SQL used.

Another problem i encountered is that. in Chk_8 all the values i want it to have is there. But for Chk_7, it is completely empty

 

[villy]

In that case this should work:

Sub newsheet()
For i = 1 To Sheets.Count - 1
    nem = "Chk_" & i
    On Error Resume Next
    If Sheets(nem) Is Nothing Then
        Sheets.Add after:=Sheets(i)
        Sheets(i + 1).Name = nem
        Sheets(nem).Range("A1") = "Failure_Class"
        Sheets(nem).Range("A3") = "Item Category"
        Sheets(nem).Range("A4") = "Actual"
    End If
Next
End Sub

 

[somenoob]

wow thanks alot. it works like how i wanted. THANKS!

 

[villy]

Thanks for feedback