add a numerated worksheet

[lior03]

hello
i have a workbook with a Sheet as an invoice .i named this sheet as one(1).how can i add a code so every time i enter the workbook a msgbox will appear asking wheter i want to copy the last sheet name it numerically 1 or 2 and so on, and finally clear all data but leave a form ready to fill again?
thanks

[mdmackillop]

A message box on entering sounds like an irritation to me, but can be done. Regarding clearing data, a sample would be useful. How is "data" to be recognised?
Have you any of your own code to show what you've attempted?

[Bob Phillips]

Private Sub Workbook_Open()
Dim ans

ans = MsgBox("Copy last sheet (Y/N)?", vbYesNo + vbQuestion, "Copy Sheet")
With ThisWorkbook
If ans = vbYes Then
ans = InputBox("Supply the sheet name", "Copy Sheet")
If ans <> "" Then
.Worksheets.Add after:=.Worksheets.Count
ActiveSheet.Name = ans
End If
End If
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code


Don't understand the bit about the form. Maybe just



myForm.Show

[lior03]

my suggestion:


Sub copy_last()
If MsgBox("would you like to duplicate last sheet?", vbInformation + vbOKCancel, "copy last sheet") = vbOK Then
Application.ScreenUpdating = False
Dim i As Integer
For i = Sheets.Count To 1 Step -1
If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then
Sheets(i).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = i + 1
Exit Sub
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

[mdmackillop]

Hi Moshe.
I think he wants the name in text. One, Two etc.

[RonMcK]

Moshe,

Why not create a standard invoice format with all the variable data fields blank (cust#, date, cust name, bill address, ship address, detail lines, etc), place this worksheet as the first one in your workbook, and set visible = false so no one messes it up for you. Then, write code to copy the blank std form whenever you want to write a new invoice. This will save time since you will not need to scrub the customer and sale specific data from the copy of the last invoice you created before you can start filling in the new invoice.

You could place the std invoice form (template) and the vba code in a separate worrkbook from the one(s) created to hold the actual customer invoices. This lets you maintain the invoice format in one place; any change to the format applies to the next and all subsequent invoices that you write.

Just thoughts for maintaining some sanity in the 'process'. :)

[RonMcK]

Hi Moshe.
I think he wants the name in text. One, Two etc.

Malcolm,

I believe Moshe asked the original question/posed the problem.

Cheers!

[mdmackillop]

Some days I'm easily confused!:bug: