Hidden sheet is being used when i am using my custom auto renaming sheets button, how to ignore it

[qarbiq]

Please help with the following issue that i have

I have 1 visible sheet which is named ECOSHEET 1 , ECOSHEET 1 has two button in it . ( i will explain what those do )
I also have 1 hidden sheet that i named it ECOSHEET X

those two buttons in the first sheet does the following

button # 1 when i click it taken ECOSHEET X and copies it to ECOSHEET X2 ( But this time it is visible ) , i can click as much as i want to add more pages like ECOSHEET X2, ECOSHEET X3, etc...

Button # 2 when i click it takes all the sheets and renames them the way i want like ECOSHEET 1, ECOSHEET2, ECOSHEET 3, etc... it also fixed the order if they are moved around

What is my problem ???

The code below is the code i use for button #2 but the problem is when i click on it it considers the hidden sheet when it reorders them and when it renames them , so this is happening in the tabs ECOSHEET1, ECOSHEET3,ECOSHEET4, etc............ The problem is it considers the hidden sheet and it renames the hidden sheet to ECOSHEET 2 which is hidden, i need to take that out , so it ignores my hidden sheet (since i use the hidden sheet as a sample to get a copy , can someone let me know what modification i need to do to fix this please ?????

I am new to VBA coding and i am not sure how to do this. Please help


Sub Button342_Click()
Dim r As Integer
bsheetexists = False
For i = 2 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & i Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 2 To Sheets.Count
On Error Resume Next
Sheets(r).Name = "ECOSHEET" & " " & r
Next r
Next i
Dim k As Integer
Dim t As Integer
For k = 1 To Sheets.Count
For t = 1 To Sheets.Count - 1
If UCase$(Sheets(t).Name) > UCase$(Sheets(t + 1).Name) Then
Sheets(t).Move After:=Sheets(t + 1)
End If
Next t
Next k
50 End Sub

 

[qarbiq]

It moves the renamed sheet as the hidden sheet, the hidden sheet needs to be untouched .

 

[dispelthemyth]

The below should work, but you can't manually reoder the hidden tab as i retrieve the name by getting the name of the last sheet, so if you reordered yourself it would assume the last sheet is the one that you dont want to rename

<Code>
Sub Button342_Click()
Sheets(Sheets.Count).Visible = True
Sheet2Name = Sheets(Sheets.Count).Name


Dim r As Integer
bsheetexists = False
For i = 2 To Sheets.Count - 1
If Sheets(i).Name = "ECOSHEET" & i Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 2 To Sheets.Count - 1
On Error Resume Next
Sheets(r).Name = "ECOSHEET" & " " & r
Next r
Next i
Dim k As Integer
Dim t As Integer
For k = 1 To Sheets.Count
For t = 1 To Sheets.Count - 1
If UCase$(Sheets(t).Name) > UCase$(Sheets(t + 1).Name) Then
Sheets(t).Move After:=Sheets(t + 1)
End If
Next t
Next k
Sheets(Sheet2Name).Move After:=Sheets(Sheets.Count - 1)
Sheets(Sheets.Count).Visible = xlHidden
Sheets(1).Activate


End Sub</Code>

Thank you so much for your time... I tried it it looks working perfectly . Except there is one problem , when i am adding pages, its totally fine .... Then i reorder pages and hit update it updates with no problem , ( and still the hidden sheet is there with the name i want ) but the little problem is when i lets say change the name of a sheet for a test, lets say i rename ECOSHEET 3 to xdfggg something like this, and then again i hit update ! this time it fixes all pages again with order and names but the thing is when i look at my hidden sheet, the sheet name is changeing from its original to xdfggg , is this an easy fix ?

Again thank you so much you helped me so much and i appreciate your time

 

[dispelthemyth]

See the latest post above, the hidden sheet will retain its name as long as its the last tab, which the code does for you

It moves the renamed sheet as the hidden sheet, the hidden sheet needs to be untouched .

 

[qarbiq]

Thank you so much sir, working perfectly as i wanted it . looks like everything looks the way i want . THANK YOU SO MUCH you helped above and beyond .. Thanks again