Sequential Invoice...with a twist.

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
OK, HERE'S WHERE I'M AT, thanks! ...

1. The first thing I'm checking out is the invoice no. Um *on me* doesn't seem to be changing when I PRINT/save/reopen or any combination. Just same invoice # keeps showing. My cell # is L22, so I naturally changed the 4 instances of a1 to L22 (used the upper case "L" by the way); hope that's ok.

I did NOT change any of the other items you had in red (ie the workbook names or sheets). I know what a sheet is, but is a "workbook" the same as the actual excel file? sorry for such a lame question.

Thanks and eager to note your responses to see what I'm doing wrong. thanks 10x so far.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Type this formula into any cell:
Code:
=CELL("filename")
press enter


copy and then right click that cell, chose paste special, then choose values.

copy and paste that into a post here.
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Yes,

I would say the workbook is the collection of sheets and the name of it is the filename like yourworkbook.xlsm.
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I just reread your previous post. Only when you run this macro will anything happen.

Here is what is intended:
Your invoice should print, then a copy should be placed into another workbook you have and rename that sheet (tab)to its inv. num
then the original sheet should change the inv. number

If this becomes accomplished then maybe adding more code to "clear or reset the invoice sheet" saving you from having to delete cells contents.


If you post your filepaths (results of ///=cell("filename")///)I will help plug them in. also the filepath to your log workbook.
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
R-man. Yeah, for sure, when you said

"I just reread your previous post. Only when you run this macro will anything happen.

Here is what is intended:
Your invoice should print, then a copy should be placed into another workbook you have and rename that sheet (tab)to its inv. num
then the original sheet should change the inv. number

>> Yeah, you totally understand the tasks on this. Sadly, none of that happens for me my friend.

If this becomes accomplished then maybe adding more code to "clear or reset the invoice sheet" saving you from having to delete cells contents.


If you post your filepaths (results of ///=cell("filename")///)I will help plug them in. also the filepath to your log workbook. "



>>Here's my path for the folder containing all the invoices is here:

C:\Documents and Settings\my-user-name\My Documents\Jdoc\INVOICES 7-2-10

Would it be at all possible to share a typical invoice xls file with you, and you can see with your own eyes what my prob is? I'm really interested to know (and of course excited to see it in action).

I really appreciate you!
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello thankyou,

I need still for you tell me what your workbook name is like...

Invoices.xlsm

Here is a way to get that:
Please type this into a any cell in your sheet and then tell me what its results are.

Code:
=cell("filename"[CODE]
(you can delete it when done)
 
Also where are your copies going to?  If another workbook then also repeat the above.
 
 
 
If you would like, upload your file to a place like box.net and provide the link.
 
Note:  You will need to make sure that your workbook is saved as a macro enabled workbook.  If unsure goto save as do that.
 

thankyou

Board Regular
Joined
Aug 13, 2008
Messages
189
oh for me, it's pretty simple. my xls filename always has one sheet I use, so let's just call my invoice or filename "Invoice".

The idea remember is that if I have. say, 25 different invoices within 1 folder, I want the one I pick (reflecting whatever of the 25 products the customer ordered) to show that new invoice number ON PRINTING, then archive that under the new invoice name elsewhere. I think we understand each other.

I can call the Invoice anything, for now, and I can call the Folder within which all are found "Folder". Doesn't matter. Hope that helps.

However, when I past the code, and hit "PRINT", the macro should run automatically (I shouldn't have to go in and find the macro and hit "RUN" right?). I want it automatic. WHen I hit PRINT though, the invoice number and none of the other stuff happens.

What am I doing wrong?
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
I sent you a p.m. You can find that under the "Quick Links" in the blue bar above. Email me your file and I will have a look.


The idea remember is that if I have. say, 25 different invoices within 1 folder
I may be misunderstanding. Why not have them all in one excel workbook?

I am not sure this can be done just by hitting "print", however I will look into that and if possible will make changes.

You can try this macro by pressing Alt+f8 and then select this macros name and click run.

However, when I past the code, and hit "PRINT", the macro should run automatically (I shouldn't have to go in and find the macro and hit "RUN" right?).
Wrong, This all can be done through a button or a keystroke shortcut like ctrl + shift + P...then all should happpen (Print, make a copy, change inv.. num. etc.) So you will not have to hit "print".

If you get an error, post back the highlighted line of code and any error msg.
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Here might be a way to use the before print event (which should happen when "PRINT" is hit). This goes into a differnt spot.

Alt + f11
at the left pane you find "this workbook"
dbl. click and then copy / paste.
note: this still needs the red feilds changed
note: still uses the workbookopen macro posted earlier, which should be in a regula module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False

Cancel = True
'Prints the active sheet
ActiveSheet.PrintOut

'Check if workbook is open
If Not WorkbookOpen("2010 Rec Inv record log1.xlsm") Then
Workbooks.Open ("C:\Users\standard account\Documents\2010 Rec Inv record log1.xlsm")
End If

'Copy invoice to logbook and then rename worksheet tab to invoice number
Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)
ActiveSheet.Name = Range("l22").Value


Windows("bid.xlsm").Activate
'Increase invoice number by random number between 10, 20
Dim mystring As String
mystring = Range("l22")
Range("l22").Value = Right(mystring, Len(mystring) - 1)
Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)
'For Each w In Application.Workbooks
' w.Save
'Next w
Application.EnableEvents = True
End Sub
 

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Here is one that I did get to work by hitting "PRINT".

This needs to go into ThisWorkbook
You can find that like this:

Press alt + f11
look to the left hand side on the tree
Find VBAProject(Yourworkbook.xlsm)
below the sheets listed should be ThisWorkbook
Double click that and then paste this in:

Code:
Private Sub Workbook_beforePrint(Cancel As Boolean)
 
MsgBox ("This shows that the Print Invoice Program is Running")
Application.EnableEvents = False
Cancel = True
MsgBox ("Printing Invoice")
 
'''''Prints the active sheet
ActiveSheet.PrintOut
 
'''''Check if workbook is open
'If Not WorkbookOpen("2010 Rec Inv record log1.xlsm") Then
 '   Workbooks.Open ("C:\Users\standard account\Documents\2010 Rec Inv record log1.xlsm")
'End If
 
'''''Copy invoice to logbook and then rename worksheet tab to invoice number
'Sheets("Invoice").Copy after:=Workbooks("2010 Rec Inv record log1.xlsm"). _
 '       Sheets(Workbooks("2010 Rec Inv record log1.xlsm").Sheets.Count)
  '      ActiveSheet.Name = Range("l22").Value
 
  MsgBox ("Increasing Invoice number by a random number between 10 & 20")
 
 'Windows("bid.xlsm").Activate
'''''Increase invoice number by random number between 10, 20
Dim mystring As String
    mystring = Range("l22").Value
        Range("l22").Value = Right(mystring, Len(mystring) - 1)
        Range("l22").Value = "R" & Range("l22").Value + Application.WorksheetFunction.RandBetween(10, 20)
'For Each w In Application.Workbooks
'    w.Save
'Next w
Application.EnableEvents = True
End Sub
This should Print, increase invoice number.

Still not finished yet closer.
 

Forum statistics

Threads
1,089,330
Messages
5,407,613
Members
403,154
Latest member
Tebrica

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top