How to force Excel to recognise Sheet3 as Sheet45 and do the needful.

[James Perry]

I need2 incorporate some data & formatting from Sheet1 thru' Sheet45.
First, i open all 45 sheets.
Next, click on Sheet1.
Keeping the Shift button pressed, i select Sheets2 thru' 45. That's a group selection.
Now that i've got my 45 sheets selected, whatever data & formatting i do in Sheet1 will be auto-done in all the remaining sheets.
Here's my question-
By default, Excel puts in Sheet1, Sheet2, Sheet3.
I tried renaming Sheet3 as Sheet45 hoping that Excel would insert the remaining sheets in between with the text+formatting (based on Sheet1).
But it did not work.
Is there a way to do this. That will be a huge time-saver whether its 45 sheets or 99.
Thanks, everyone.

 

[Deepak]

Try this..

Option Explicit
Sub NewSheets()
Dim w As Worksheet, i As Long
Dim dictionary As Object

Application.ScreenUpdating = False
    With CreateObject("scripting.dictionary")
        For Each w In ActiveWorkbook.Sheets
            .Item(w.Name) = True
        Next w
        For i = 1 To 99
            If .Item("Sheet" & i) = True Then
            Else
                Sheets.Add().Name = "Sheet" & i
                'Sheets("Sheet" & i).Columns("A:A").ColumnWidth = 11 'Your format
            End If
        Next i
    End With
'or format all
For Each w In ThisWorkbook.Sheets
    With w
        .Columns("A:A").ColumnWidth = 11
    End With
Next w
Application.ScreenUpdating = True
End Sub

 

[Deepak]

If you wish to simply add duplicate sheets then use it.

Sub Add_MultipleSheets()
Dim i AsLong
   For i = 1 To 99 'Change as required
       'Change the sheet name which duplicate's needs to add. 
        Sheets("ABCD").Copy After:=Sheets(i) 
   Next
EndSub

 

[James Perry]

Thanks, Deepak. Very much appreciated.

 

[Deepak]

Welcome! Glad it helped!!!