How would I reindex worksheet numbers using VBA code?

Ex.

sheet1, sheet7, sheet11, to sheet1, sheet2, sheet3

Sub Renamesheets()
Dim i As Long, j As Long
Dim k As Long, bQuit As Boolean
l = 0
For i = 1 To 26
For j = 1 To 26
For k = 1 To 27
l = l + 1
Worksheets(l).Name = Chr(i + 64) & _
Chr(j + 64) & Chr(k + 64)
If l = Worksheets.Count Then
bQuit = True
Exit For
End If
Next
If bQuit Then Exit For
Next
If bQuit Then Exit For
Next
For i = 1 To Worksheets.Count
Worksheets(i).Name = "Sheet" & i
Next

End Sub

--
Regards,
Tom Ogilvy


"GaryMay" wrote:

How would I reindex worksheet numbers using VBA code?

Ex.

sheet1, sheet7, sheet11, to sheet1, sheet2, sheet3

In the Project Box in VBA I had:
sheet1(name)
sheet19(name)
sheet2(name)
sheet21(name)
sheet22(name)
sheet3(name)

Your code gave me:
sheet1(AAB)
sheet19(AAA)
sheet2(AAE)
sheet21(AAH)
sheet22(AAG)
sheet3(AAC)

I need it to reindex like this say:
sheet1(name)
sheet2(name)
sheet3(name)
sheet4(name)
sheet5(name)
sheet6(name)


Or, to reset so that the next sheet will always be sheet7.

Thanks for the effort.







"Tom Ogilvy" wrote:

Sub Renamesheets()
Dim i As Long, j As Long
Dim k As Long, bQuit As Boolean
l = 0
For i = 1 To 26
For j = 1 To 26
For k = 1 To 27
l = l + 1
Worksheets(l).Name = Chr(i + 64) & _
Chr(j + 64) & Chr(k + 64)
If l = Worksheets.Count Then
bQuit = True
Exit For
End If
Next
If bQuit Then Exit For
Next
If bQuit Then Exit For
Next
For i = 1 To Worksheets.Count
Worksheets(i).Name = "Sheet" & i
Next

End Sub

--
Regards,
Tom Ogilvy


"GaryMay" wrote:

How would I reindex worksheet numbers using VBA code?

Ex.

sheet1, sheet7, sheet11, to sheet1, sheet2, sheet3

first, no my code wouldn't do what you said unless you altered it. It gives
the 3 letter names as intermediate values to avoid naming conflicts. It then
renames all the sheets as sheet1, sheet2, sheet3, etc.

This does what almost anyone here would say your post asks for.

Now it appears you want to talk about the codename of the sheet. That was
my original assumption in the first thread you started related to this and I
have you a reference to Chip Pearson's page with the proper code to use to
rename the codename of a sheet.

You could combine that code with the code I posted in this thread to
accomplish what you want to do.

--
Regards,
Tom Ogilvy




"GaryMay" wrote:

In the Project Box in VBA I had:
sheet1(name)
sheet19(name)
sheet2(name)
sheet21(name)
sheet22(name)
sheet3(name)

Your code gave me:
sheet1(AAB)
sheet19(AAA)
sheet2(AAE)
sheet21(AAH)
sheet22(AAG)
sheet3(AAC)

I need it to reindex like this say:
sheet1(name)
sheet2(name)
sheet3(name)
sheet4(name)
sheet5(name)
sheet6(name)


Or, to reset so that the next sheet will always be sheet7.

Thanks for the effort.







"Tom Ogilvy" wrote:

Sub Renamesheets()
Dim i As Long, j As Long
Dim k As Long, bQuit As Boolean
l = 0
For i = 1 To 26
For j = 1 To 26
For k = 1 To 27
l = l + 1
Worksheets(l).Name = Chr(i + 64) & _
Chr(j + 64) & Chr(k + 64)
If l = Worksheets.Count Then
bQuit = True
Exit For
End If
Next
If bQuit Then Exit For
Next
If bQuit Then Exit For
Next
For i = 1 To Worksheets.Count
Worksheets(i).Name = "Sheet" & i
Next

End Sub

--
Regards,
Tom Ogilvy


"GaryMay" wrote:

How would I reindex worksheet numbers using VBA code?

Ex.

sheet1, sheet7, sheet11, to sheet1, sheet2, sheet3

No problem my friend. I did not change your code. Thanks for your effort.

"Tom Ogilvy" wrote:

first, no my code wouldn't do what you said unless you altered it. It gives
the 3 letter names as intermediate values to avoid naming conflicts. It then
renames all the sheets as sheet1, sheet2, sheet3, etc.

This does what almost anyone here would say your post asks for.

Now it appears you want to talk about the codename of the sheet. That was
my original assumption in the first thread you started related to this and I
have you a reference to Chip Pearson's page with the proper code to use to
rename the codename of a sheet.

You could combine that code with the code I posted in this thread to
accomplish what you want to do.

--
Regards,
Tom Ogilvy




"GaryMay" wrote:

In the Project Box in VBA I had:
sheet1(name)
sheet19(name)
sheet2(name)
sheet21(name)
sheet22(name)
sheet3(name)

Your code gave me:
sheet1(AAB)
sheet19(AAA)
sheet2(AAE)
sheet21(AAH)
sheet22(AAG)
sheet3(AAC)

I need it to reindex like this say:
sheet1(name)
sheet2(name)
sheet3(name)
sheet4(name)
sheet5(name)
sheet6(name)


Or, to reset so that the next sheet will always be sheet7.

Thanks for the effort.







"Tom Ogilvy" wrote:

Sub Renamesheets()
Dim i As Long, j As Long
Dim k As Long, bQuit As Boolean
l = 0
For i = 1 To 26
For j = 1 To 26
For k = 1 To 27
l = l + 1
Worksheets(l).Name = Chr(i + 64) & _
Chr(j + 64) & Chr(k + 64)
If l = Worksheets.Count Then
bQuit = True
Exit For
End If
Next
If bQuit Then Exit For
Next
If bQuit Then Exit For
Next
For i = 1 To Worksheets.Count
Worksheets(i).Name = "Sheet" & i
Next

End Sub

--
Regards,
Tom Ogilvy


"GaryMay" wrote:

How would I reindex worksheet numbers using VBA code?

Ex.

sheet1, sheet7, sheet11, to sheet1, sheet2, sheet3