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

 

[dispelthemyth]

A simple change, making the i = 2 and r = 2 both 3 will mean the 1st and 2nd sheet are not renamed (Untested)

<code>

Sub Button342_Click()
Dim r As Integer
bsheetexists = False
For i = 3 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & i Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 3 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

</Code>

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]

A simple change, making the i = 2 and r = 2 both 3 will mean the 1st and 2nd sheet are not renamed (Untested)

<code>

Sub Button342_Click()
Dim r As Integer
bsheetexists = False
For i = 3 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & i Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 3 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

</code>

thank u for the help, changing i and r to 3 does not rename the hidden sheet but the problem is my second visible sheet is still becoming ECOSHEET3 , it has to say ECOSHEET2 , do you know how to fix this ?

 

[dispelthemyth]

Try the below, just tested it, the 1st 2 sheets retain there names and the next for were 2 - 5

<Code>

Sub Button342_Click()
Dim r As Integer
bsheetexists = False
For i = 3 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & i - 1 Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 3 To Sheets.Count
On Error Resume Next
Sheets(r).Name = "ECOSHEET" & " " & r - 1
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


</Code>

 

[qarbiq]

Thank you so much it works perfectly fine, can i ask you my last question about the ADD button that i have ? ( the one that takes the hidden sheet and copies it ? )

please look at my current code for the other button, right now it takes the hidden sheet and it copies it to the new sheet, but i want it when its added it says ECOSHEET 2, ECOSHEET 3, ETC....... ( so in the right order and name ) but my code seems not working correctly it was working good when the sheet was visible, but i need the sample sheet be hidden and everytime the copy being made from the hidden sheet so here is my code, if you help me with this all my problem will be solved !!! Thank you so much here is the code for the add button


Sheets(2).Copy After:=Sheets(Sheets.Count)
bsheetexists = False
For i = 1 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & " " & i Then bsheetexists = True
Next i
Dim test As Worksheet
If bsheetexists = False Then
On Error Resume Next
Set test = ActiveSheet
test.Name = "ECOSHEET" & " " & i

End If
end sub

 

[dispelthemyth]

Try the below, i think you code was having issues as it created a copy of sheet(2) which was not visible so it was changing the name of a different sheet, not the one you created

Press Alt + F11 and check if you have any sheets in there you do not expect (They will not be visible on the spreadsheet)

<code>
Sub test()
Application.ScreenUpdating = False


Sheets(2).Visible = True
Sheets(2).Copy After:=Sheets(Sheets.Count)
bsheetexists = False
For i = 1 To Sheets.Count
If Sheets(i).Name = "ECOSHEET" & " " & i Then bsheetexists = True
Next i
Dim test As Worksheet
If bsheetexists = False Then
On Error Resume Next
Set test = ActiveSheet
test.Name = "ECOSHEET" & " " & i - 2


End If
Sheets(2).Visible = xlHidden
Application.ScreenUpdating = True
End Sub
</code>

 

[qarbiq]

thanks again for the help

for the first time i click add a sheet, it takes the hidden sheet and copies it to ECOSHEET X2, etc,,,, then i click the update button (button#1) it fixes everything, but now if i come back and click the add button for some reason it takes another sheet and makes it hidden ( so this time i have 2 sheet unhidden , i am not sure what is happening , and no there is not other sheet that i am expecting.....

 

[qarbiq]

its working fine when i am using the add button for once, after i hit the update button, it fixes all orders and even the hidden sheet is still there, but after that if i hit the add button to add another sheet for some reason its taking another sheet and making it hidden, i think i need to update something inside the button number 2 ( update button ) is that right ? i can't think of anything else. looks like when it updates the pages, somehow it makes other sheet sheet number 2 so next time i hit the add button it treats differently . i don't know .... Do you think you can help me with this ? sorry for all my questions....

 

[dispelthemyth]

Try the below, it appears to do as needed, i just move your hidden sheet to be the last sheet and seems to work

<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
If Sheets(i).Name = "ECOSHEET" & i - 1 Then bsheetexists = True
If bsheetexists = False Then
End If
For r = 2 To Sheets.Count
On Error Resume Next
Sheets(r).Name = "ECOSHEET" & " " & r - 1
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


Sub testName()
Application.ScreenUpdating = False


Sheets(Sheets.Count).Visible = True
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count - 1)
Sheets(Sheets.Count).Visible = xlHidden
bsheetexists = False
For i = 2 To Sheets.Count - 1
If Sheets(i).Name = "ECOSHEET" & " " & i And bsheetexists = True Then
GoTo 1
Else
Sheets(i).Activate
ActiveSheet.Name = "ECOSHEET" & " " & i
End If
1
Next i
Application.ScreenUpdating = True
End Sub
</code>

its working fine when i am using the add button for once, after i hit the update button, it fixes all orders and even the hidden sheet is still there, but after that if i hit the add button to add another sheet for some reason its taking another sheet and making it hidden, i think i need to update something inside the button number 2 ( update button ) is that right ? i can't think of anything else. looks like when it updates the pages, somehow it makes other sheet sheet number 2 so next time i hit the add button it treats differently . i don't know .... Do you think you can help me with this ? sorry for all my questions....

 

[qarbiq]

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