VBA Novice trying to improve a monotonous work task

[khamilton007]

Hi All,
first time posting

currently as part of my job I create an excel with many tabs... sometimes as many as 150.


I have several questions.

Firstly, the tab structure is such that the first 8-10 (this can vary) tabs have specific names from there the sub sequence tabs then follow a numbering system of tab_10...tab_20 incrementing by 10 each tabs that jumps to the next. IS there a VBA Code i can add that can do the following.

Starting @ tab_10 increase in increments of 10 the name on all sheets until the end.

The next one is similar to the above is specific to a cell contained in the same position in each of those tabs?

Starting @ tab_10 increase in increments of 10 cell B4 on all sheets until the end.

forgive me if this is basic stuff but ive never done VBA before and am trying my best to learn

 

[saboh12617]

Hello,

Yes it is possible. Just a few questions :

Do you want the cell B4 on each sheet to have the name of its own sheet ? If so you can do it with a formula with the CELL function (it's also possible in VBA to write it "hard", if you prefer).

How are you sheets named previously?
I ask this because you did not specify how to detect where to start the renaming. Is, for example, the ultimate "static" sheet always named the same? Or are the tabs already named tabsXXX?

For the moment i understood that the program should detect the sheet named "tab_10", and rename the followings. Below is a sample:

Sub RenameSheets()
  Dim iStart As Long
  iStart = ThisWorkbook.Worksheets("tab_10").Index
  
  Dim i As Long
  With ThisWorkbook
  For i = iStart To .Worksheets.Count
    With .Worksheets(i)
      On Error Resume Next
      .Name = "tab_" & 10 * (i - iStart + 2)
      On Error GoTo 0
      .Range("B4").Value2 = 10 * (i - iStart + 1)
    End With
  Next i
  End With
End Sub

 

[saboh12617]

Edit :

.Name = "tab_" & 10 * (i - iStart + 1)

Not + 2