• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

adjusting code transfer data from invoice to archive sales

alhagag

Member
HI, GUYS


i have file actually i download from the internet but, it doesn't work the file contains sheet("invoice ") and sheet("sales")

when i fill data in sheet("invoice ") and press macro new invoice it doesn't save in sheet("sales") and doesn't give me anew number invoice
i need help somebody
 

Attachments

Logit

Active Member
See if this works for you ...




Code:
Option Explicit

'This also illustrates the method of referring to cells
'by using "Shortcut Notation" where, for example,
'Range("A1") can be written simply as [A1]

Sub PrintInvoice()
      Dim Response
AnotherCopy:
      Sheet1.[A1:I44].PrintOut
      Response = MsgBox("Do you need another copy?", vbYesNo + vbQuestion, "Confirmation")
      If Response = vbNo Then
            FillSalesList
            NewInvoice
      Else
            GoTo AnotherCopy
      End If
      Sheet1.Unprotect
      'Increment the invoice number
      [G3] = [G3] + 1
      Sheet1.Protect
End Sub

'This saves details of the invoice on another sheet
Private Sub FillSalesList()
      With Sheets("Sales").Columns(1).Rows(65536).End(xlUp)
            .Offset(1, 0) = Sheet1.[G3]
            .Offset(1, 1) = Sheet1.[G7]
            .Offset(1, 2) = Sheet1.[G5]
            .Offset(1, 3) = Sheet1.[I42]
            .Offset(1, 4) = Sheet1.[I43]
            .Offset(1, 5) = Sheet1.[I44]
            .Offset(1, 6) = Sheet1.[G1].Text
      End With
End Sub

'Clears the invoice sheet
Sub NewInvoice()
FillSalesList
      With Sheet1
            .Unprotect
            Cells.Locked = False
            'Lock all the cells with formulae etc.
            [A11:I11, F1:F10, G3, H42:I44].Locked = True
            'Clear details of last sale
            [A12:I40, G4:G10].ClearContents
            [G3] = [G3] + 1
            [B12].Select
            .Protect
      End With
End Sub
 

Attachments

alhagag

Member
thanks so much it's perfect but, i would understand what you did until work i noticed just changing in macro new invoice but the others no changes could you explain me , if you don't mind ?
 

Logit

Active Member
The NEW INVOICE button is connected to the macro : NEW INVOICE.

The original NEW INVOICE macro did not have the ability to "write" the invoice data to the SALES worksheet, nor did it allow
for increase the invoice number by one, for the new blank invoice.

Adding this :

Sub NewInvoice()
FillSalesList '<--- ADDED THIS
With Sheet1

.. to the macro causes it to 'call' the macro FillSalesList first, before it clears the invoice of all data. The FillSalesList macro is
responsible for 'writing' the data to the SALES worksheet. After doing that, it continues on with the rest of the macro as
previously written until it gets to this line :

[A12:I40, G4:G10].ClearContents
[G3] = [G3] + 1 '<--- ADDED THIS
[B12].Select

The new line auto-increments the Invoice Number for the next new invoice.

Nothing else was changed or edited in the existing macros.
 
Top