I have created a system auto-add a new invoice to worksheet. I am trying to make this as user friendly as possible and have used the 'no duplicates' code in several of my commands.
But then I thought it would be much easier to just have the new worksheet created, auto-generate an invoice number. Because although this command gives 2 warnings, it still saves the worksheet initially as the first invoice ref typed in "C10". If the user does not resave the invoice after re-input it will save invoice ref as duplicate. I have tried several avenues, and cannot get this to work.
I have been at this all night, and can not come up with a way to add the previous sheet cell ref, (which will be saved by user) and add 1 to create the new cell ref. My last ref is TT913 (so previous sheet to NewSheet created). This shows the last of my roads to research.
Please can anyone help?
The code I have so far is:
Worksheets("Activesheet").Copy before:=Sheets("Master")
ActiveSheet.Name = "NewSheet"
Dim Ws As Worksheet
Set Ws = Worksheets("NewSheet")
Worksheets("NewSheet").Range("e10") = Now
Ws.Unprotect Password:="password"
ActiveSheet.CommandButton1.BackColor = &H8080FF
Function PrevSheet(RCell As Range)
Dim xIndex As Long
Application.Volatile
xIndex = [c10]Worksheet.Index
If xIndex > 1 Then _
PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function
End Sub
I have tried several times to upload the file, but even though i have removed all personalised worksheets/ data and vba it is still telling me its 'too large'
Bookmarks