This will automatically number your reports, based on the number of sheets
in the workbook. If you have three sheets when you click the button, the
report number would be four. You can manage the report number by
subtracting or adding to Sheets.Count.

Sheets(Sheets.Count).Name = "Report" & Sheets.Count - 2

would number the report at two less than the number of sheets, etc.


Sub Button135_Click()
Dim strSheetName As String
Sheets("Report1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Report" & Sheets.Count
End Sub


I don't understand the other part of your post.


"Gary" wrote in message
...
Hi,

I need to be able to create a button, that when pressed will copy the
current worksheet into a new worksheet. The first worksheet is to be
called 'Report1', the next worksheet needs to be called 'Report2' and
so on. I have adpated some code I have access to and so far have the
following:

Sub Button135_Click()
Dim strSheetName As String

strSheetName = InputBox("Enter sheet name")
Sheets("Report1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = strSheetName

End Sub

I want to get rid of the Input box asking for the name of the
worksheet and automate this so that it goes Report1, Report2,
Report3........

Is this possible?

Also, on the newly created sheets some of the fields need to be a
running total from the sheet previous. Hoew can I get these to update?

Thanks.