SAME NAME FOR ALL SHEETS

[K]

Hi all, I have button on sheet which have macro allocated (see below)

Sub addsht ()
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet
..Name = "EXTRACTED DATA"
End With
End Sub

The problem is that when I press this button once it works fine but
when I press it again it gives error on
..Name = "EXTRACTED DATA"
what code line I can add so that when I press the button it give
sheets name like "EXTRACTED DATA (1) , EXTRACTED DATA (2) and so on.
in other words keeping the same name but adding 1 , 2 , 3..... in the
end. Please can any friend can help

 

[Bernard Liengme]

This will work for a new workbook
Dim j As Integer
Sub addsht()
Sheets.Add After:=Sheets(Sheets.Count)
j = j + 1
ActiveSheet.Name = "EXTRACTED DATA (" & j & ")"
End Sub

By defining J outside the sub, its value is preserved each time the sub is
run
However, J goes back to 1 when the file is opened tomorrow

best wishes

 

[Peter T]

Another one -

Sub test2()
Dim i As Long
Dim s As String
Dim ws As Worksheet

On Error Resume Next
Err.Clear
i = 0
While Err.Number = 0
i = i + 1
s = "EXTRACTED DATA (" & i & ")"
Set ws = ActiveWorkbook.Worksheets(s)
Wend
On Error GoTo 0

With ActiveWorkbook
Set ws = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
ws.Name = s
End With

End Sub


If you have, say, sheets 1, 2 and 4, the above will insert sheet "3". That
may or may not be a useful "feature" if "3" had been deleted.

Regards,
Peter T