Invoice Number + 1, Auto Appear

[Topazpower]

Cell D15 is my Invoice Number Field.

I have entered my invoice number starting from 001. I am doing this manually every time after referring the previous Invoice Number in the next sheet( i.e., Sheet 2)

I have a macro "SAVE" in Invoice Page, which copies the S.No, Name , Qty and Amount from the Invoice page to the next Sheet i.e., ( Sheet 2). This copies row by row in the next page after every Invoice is done.
After Copying the above said datas, I have another Macro "NEW" which deletes the S.No Field, Name, Qty and Amount Field for keeping the Invoice ready for the next Feed.

Is it possible to auto increase the invoice number + 1 in the Invoice Filed, based on the copied Invoice number in the Next Sheet ( Sheet 2 ). Provided, hitting the "New" button for the next feed should not affect the S.No Field.

Kindly Help.

Jane

    Sub DeleteStuff()
    Dim c As Range
    For Each c In Range("D15,C18:C20")
        If Not IsNumeric(c) Then c.ClearContents
        If IsNumeric(c) Then c.ClearContents
    Next c
End Sub

Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text
For x = 1 To r
If .Cells(x, 2).Text = InvN Then
MsgBox ("Receipt " + InvN + " has already been Saved")
GoTo endd
End If
Next x
For a = 2 To 8
Select Case a
Case 2
.Cells(r, a) = Cells(15, 4)
Case 3
.Cells(r, a) = Cells(18, 3)
Case 4
.Cells(r, a) = Cells(20, 3)
Case 5
.Cells(r, a) = Cells(22, 3)
Case 6
.Cells(r, a) = Cells(24, 3)
Case 7
.Cells(r, a) = Cells(26, 3)
Case 8
.Cells(r, a) = Cells(13, 4)
End Select
Next a
End With
MsgBox ("       Data Saved")
endd:
End Sub

 

[shyrath]

Hi Jane,

Why not combine the two? e..g. save your invoice and clear the range?
One point on the clear range (New Invoice). You do a loop asking to check each cell if it´s a number, and if it´s not a number, clear the contens, and if it IS a number, then clear the contents anyway.

So your quicker off by doing: Range("C18:C20").ClearContents

I extended your below code by incrementing D15 with 1 and then clearing C18 and D20. Hope this helps you and I understood you correctly

Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text
For x = 1 To r
If .Cells(x, 2).Text = InvN Then
MsgBox ("Receipt " + InvN + " has already been Saved")
GoTo endd
End If
Next x
For a = 2 To 8
Select Case a
Case 2
.Cells(r, a) = Cells(15, 4)
Case 3
.Cells(r, a) = Cells(18, 3)
Case 4
.Cells(r, a) = Cells(20, 3)
Case 5
.Cells(r, a) = Cells(22, 3)
Case 6
.Cells(r, a) = Cells(24, 3)
Case 7
.Cells(r, a) = Cells(26, 3)
Case 8
.Cells(r, a) = Cells(13, 4)
End Select
Next a
End With
MsgBox ("       Data Saved")

'increment InvN with 1
Cells(15, 4) = InvN + 1
'clear entry in columns (not the invoice field)
Range("C18:C20").ClearContents

endd:
End Sub

 

[shyrath]

Also, have a look at this thread. Which seems to be quite similar to what you are trying to achieve.

http://www.mrexcel.com/forum/showthread.php?p=1857932

 

[Topazpower]

Thank you very much for the Code.

Saving and Clearing the data, restricts me from printing the Invoice. Firstly I would like to have the Datas
"SAVED", "PRINT" and FINALLY "NEW"(Clear off).

If by mistake hitting TWICE the button(with your code) keep generates the invoice number by incrementing +1 and
saves a copy of the Invoice number row by row in the second sheet.

Could you please help me in designing as per the following:

Invoice number should be be generated automatically and consecutively in Cell D15

New Invoice number should appear automatically only after the "NEW" button is Clicked.

Clicking the "SAVE" button Without entering datas in the rest of the Fields, ie., in C18, C20, C22 and C24 should NOT save the Current Invoice number with the rest of the details in the next sheet. A pop up should say that "Fill all Fields" when trying to hit the Save button if a Field is blank.

Clicking "NEW" Button "TWICE" Should not generate new Invoice Number untill Unless the Datas are filled and Saved.
( Can it be done by Pulling the previous Invoice number from the next sheet. i.e where the datas are saved and incrementing the Invoice number + 1 which inturn avoid duplicating of Invoice Numbers?).

Thanks in Advance.

Jane

 

[shyrath]

Try this:

Save

Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text

'Clicking the "SAVE" button Without entering datas in the rest of the Fields,
'ie., in C18, C20, C22 and C24 should NOT save the Current Invoice number
'with the rest of the details in the next sheet. A pop up should say that
'"Fill all Fields" when trying to hit the Save button if a Field is blank.

    If Range("c18") = "" Or Range("c20") = "" Or Range("c20") = "" Or Range("c22") = "" Or Range("c24") = "" Then
    MsgBox "Please fill all required fields", vbCritical, "Missing data"
    
    Exit Sub
    End If
    

For x = 1 To r
If .Cells(x, 2).Text = InvN Then
MsgBox ("Receipt " + InvN + " has already been Saved")
GoTo endd
End If
Next x
For a = 2 To 8
Select Case a
Case 2
.Cells(r, a) = Cells(15, 4)
Case 3
.Cells(r, a) = Cells(18, 3)
Case 4
.Cells(r, a) = Cells(20, 3)
Case 5
.Cells(r, a) = Cells(22, 3)
Case 6
.Cells(r, a) = Cells(24, 3)
Case 7
.Cells(r, a) = Cells(26, 3)
Case 8
.Cells(r, a) = Cells(13, 4)
End Select
Next a
End With
MsgBox ("       Data Saved")

endd:
End Sub

New

Sub newinv()

'clear entry in columns (not the invoice field)
Range("C18").ClearContents
Range("C20").ClearContents
Range("C22").ClearContents
Range("C24").ClearContents


'increment InvN with 1
r = Sheets("Sheet2").Range("B65536").End(xlUp).Value + 1
Cells(15, 4) = r
End Sub

Print

Sub printprev()
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

 

[Topazpower]

Thank you very much. This is what I expected and works fine.

Thank you once again.

Jane.

 

[shyrath]

Your welcome, glad it worked out.