Dear sir,with best regards,enclosing herewith a sample of an invoice asking you kindly the possibility to :
- creating a code for calling any invoice mentioned in ( invoices ) worksheet.
- creating a code for printing the calling invoice.
- change the way of saving from (horizontal) to (vertical).
Many thanks in advance.
Dear sirs,with regards,any hope to get any reply.
Accept my apology.
You can a have invoice in sheet1 and store only data in next sheet2 horizontally.Originally Posted by tofimoon4
If you want to edit and save or print you can retrieve from sheet2 to invoice(sheet1).
That will be better way.
Charles
You need to save relevant data such as Date, Invoice Number, Total to a table in a sheet. For code see the Contact Form example
Save the Invoice number in Column A of the table the use VLOOKUP to populate the data back to the form when required
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Many thanks my dears for advices,i want to ask if is it possible to create a macro for printing any invoice (with its range) mentioned in ( invoices ) sheet .
My regards.
You can use this code for printing selected area as you say invoice
in a sheet20
If you are ok in preview then go for printing.
Dim ws As Worksheet Set ws = Worksheets("Invoice") Sheet20.PageSetup.PrintArea = "$B$3:$G$76" Sheet20.PrintPreview
charles
Hi
Whilst the way in which you are storing your invoices is not the preferred method, you can select and printout any invoice from your sheet with the following code, provided you make one alteration to your invoice.
I added a formula in cell I2 or your Invoice which is
=I6
I formatted the cell with a Custom format of ;;; which prevents the value from being visible.
I did the same for each of the invoices already stored.
Note, that you need to put in some error checking to ensure that you don't have too many invoices stored, to exceed the maximum number of columns in XL2003 which is 256 columns or that when you get up to that number you give a row offset to move the next invoices down the page starting again at the left of the page.
If you do this you will need to amend the Print routine accordingly.
Sub PrintInv() Dim Invno As Long, maxinv As Long, findinv As Long Invno = InputBox("Enter no of invoice to Print") If Invno = 0 Then Exit Sub maxinv = WorksheetFunction.Max(Sheets("Invoices").Range("2:2")) If Invno > maxinv Then Call MsgBox("Invoice number" _ & vbCrLf & "" _ & Invno & vbCrLf _ & vbCrLf & "does not exist" _ , vbExclamation, Application.Name) Exit Sub End If findinv = WorksheetFunction.Match(Invno, Sheets("Invoices").Range("2:2"), 0) If IsError(findinv) Then Call MsgBox("Invoice number" _ & vbCrLf & "" _ & Invno & vbCrLf _ & vbCrLf & "does not exist" _ , vbExclamation, Application.Name) Exit Sub End If Sheets("invoices").Activate With Sheets("Invoices") .Range(Cells(2, findinv - 7), Cells(44, findinv + 1)).PrintPreview End With Sheets("Save").Activate End Sub
--
Regards
Roger Govier
Microsoft Excel MVP
You've already been told the best way to do this
Many thanks my dear for help
It looks quite pretty now,
But when you try to save nearly more than 50 invoices, your file size will be
25 to 30MB. Because your every invoice have Wordart box.
So if you save your data in next sheet in row by row (horizontal).
You can save more than 5 thousand invoice.
How you do If you need to edit back one invoice?
Charles
Dear Mr.Roger Govier,many thanks for your excellent solution which will save me from big problem , as you have mentioned in your message about the limited columns in excel 2003 ,is it possible to :
- complete the saving from the row (47) and rows below .( by modify the code )
- or save the invoices vertically.
I think your genius will solve the problem.
My appreciation.
Dear sirs,with my regards,can i wait for any arrangements or consider the request as solved.
Many thanks in advance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks