Incrementing Worksheets in Excel 2K

[Wizzweb]

Hi all

I have a workbook for each customer and a worksheet for each invoice for that customer.

Is there a way I can increment each work sheet. For example: Sheet 1 would be Invoice # 2004/321 - Sheet 2 would be Invoice #2004/322 and so on?


TIA

Ken

 

[Loomah]

Hi Ken
You could run this proc ONCE providing your sheets are in order. Note that "/" is an invalid character for a sheetname. I've assumed the 2004 part of the number to be the year.

This proc adds a custom property to your book on which future invoice numbers are based. Just delete if it isn't relevant.

Sub SetSheetNames()
Dim i As Integer
Dim ws As Worksheet
Dim DocProps As DocumentProperties
Set DocProps = ThisWorkbook.CustomDocumentProperties
'initial inv num
i = 321
For Each ws In ThisWorkbook.Worksheets
    ws.Name = "#" & Year(Now) & "-" & i
    'note use "-" not "/" as / not allowed
    i = i + 1
Next
DocProps.Add Name:="InvNum", _
    LinkToContent:=False, _
    Type:=msoPropertyTypeNumber, _
    Value:=i
End Sub

If you add a new sheet for each invoice you could use this code to name the sheet. Place in the workbook module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim i As Integer
i = ThisWorkbook.CustomDocumentProperties("InvNum").Value
Sh.Name = "#" & Year(Now) & "-" & i
ThisWorkbook.CustomDocumentProperties("InvNum").Value = i + 1
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Wizzweb]

Thanks for the Code Loomah.

Not being too familiar with VBA and all dandy things like that, I take it that I post this code using the VB Editor under the Macro button?

Ken

 

[PBAPaul]

Hi Ken

Try this -

Sub InsertNewInvoiceSheet()
    Dim StartName As String
    Dim ShtNum As Integer
    Dim ShtName As String
    Dim InvNum As Integer
    Dim TempInvNum As Integer

    InvNum = 0
    StartName = "Invoice 2004 -"
    With ActiveWorkbook
        'Check each worksheet
        For ShtNum = 1 To .Sheets.Count
            'Is worksheet an Invoice?
            If Left(Sheets(ShtNum).Name, 14) = StartName Then
                'If so then get the number of the invoice
                TempInvNum = Val(Right(Sheets(ShtNum).Name, 4))
                'Check if the invoice number is the highest
                If TempInvNum > InvNum Then InvNum = TempInvNum
            End If
        Next
        'Create the new sheet name
        ShtName = StartName & " " & String(5 - Len(InvNum), "0") & InvNum + 1
        'Add a new sheet
        Sheets.Add
        'Rename the new sheet
        ActiveSheet.Name = ShtName
    End With
End Sub

You can change the line StartName = "Invoice 2004 -" to something like StartName = "Invoice " & year(now()) & " -" to make it change every calendar year.

Hope that this helps

Paul

 

[Wizzweb]

Hi Paul

Thanks for the code ..... I will certainly try it out.

Looks like I have a lot to learn!! Still, at my age, it's better than sitting around all day vegetiting!!!

Ken

 

[Wizzweb]

MMMMMMMM

Guess I need help!

Just tried both versions out, but get nowhere. I wonder if it would be possible for either of you to send me a sample so that I can look at what goes where. I have always found that I can learn by example better than wandering about.

TIA

Ken

 

[Loomah]

Ken
Post your email address (I suggest in the format Ken AT Domian DOT com rather than actual address) and I'll send you a workbook with the code in. I'll try to explain in more detail what's going on too!

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Wizzweb]

Thanks a bunch Loomah

Email is as follows .... KenATwizzwebDOTcoDOTuk

Hope that's right.

Tried doing the code bit several ways, but just keep getting a Compile Error .. the explanation of which is way beyand my grey cells!!!

Ken