Rename a worksheet

Hi guys, this macro works but is there anyway I can change the

Set W1 = Worksheets("Sheet1")

so that ("Sheet1") can be any number? possibly using the ("*") which i think relates to any number. Every time a sheet is added it goes 2,3,4 etc. At the moment this is set up to change sheet1, any help would be greatly appreciated.

Sub Addsheets()

Set W1 = Worksheets("Sheet1")

Sheetname = W1.Range("AE18").Value

    Sheets.Add After:=ActiveSheet
    
  
    W1.Name = Sheetname
End Sub

This is part of a bigger macro but i will try. On my spreadsheet there is one worksheet (Worksheet1) I want the macro to add sheets and rename them whatever is in cell AE18. When the macro adds sheets they go sheet2, sheet3 etc. Is there a way I can write a piece of code that covers everything. I can't add how many sheets i need first because this changes from month to month. I'm not sure if that explains it better?

sheet2, sheet3 are the default names created by excel. Of course you can change the name of any worksheet, but if you have fixed range(AE18), you will get an error as you have already used it and the sheet name exists.

Okay I'm with you there and I get the error message as you just said the sheet exists. For example if the name in cell (AE18) was (340) the macro would change that. Then if I changed (AE18) to (550) is there anyway of having the macro add the next sheet and change it to (550)?

There are two ways of naming sheets: string and index.
The string you now have. Index shows the position of the sheet you want to refer to.

Set W1 = Worksheets(1)

First sheet within the workbook.

Dim W1  as worksheet
Set W1 = Worksheets(1)

w1.name=range("AE18")