Macro sheet replication and numbering

[shaun111]

Hi,

Could you please help me with a piece of code which I can add to a form button to achieve the following:

1. Replicate a "super hidden sheet" invoice template once the form button is pressed. i.e add a copy of the super hidden sheet to the end of the existing sheets.

a. The replicated template will need to have as its sheet name, the code of the sheet before it plus one. For example: the workbook has the sheets, tabs: 1960, 1961 and 1962. Once the replicate sheet is added its tab will reflect 1963.

b. Is there an equation or a piece of code that will return the name of the sheet in a particular reference cell within the sheet. For example once 1963 is created cell B1 within 1963 will return "1963" as the invoice number?

Hope that is not too confusing?

Thanks

 

[VoG]

Something like this should do that

Sub test()
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = xlSheetVisible
Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
Sheets("Sheet1").Visible = xlSheetVeryHidden
With ActiveSheet
    .Name = CInt(Sheets(Sheets.Count - 1).Name) + 1
    .Range("B1").Value = .Name
End With
Application.ScreenUpdating = True
End Sub

 

[shaun111]

Perfect! Thanks so much.