| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Auto Numbering
I want my invoices to automatically go up in number. EX. Invoice 1, Invoice
2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#2
|
|||
|
|||
|
hi,
I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#3
|
|||
|
|||
|
THANKS, WORKS GREAT
"FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#4
|
|||
|
|||
|
This sound exactly what I need. How can I use the code? I am getting syntax
errors on the copy and paste. Unfortunatly I have done limited code writing, mostly creating macros, so Im slow. "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#5
|
|||
|
|||
|
hi,
you shouldn't be having errors if you made all the changes. did you? regards FSt1 "chris w" wrote: This sound exactly what I need. How can I use the code? I am getting syntax errors on the copy and paste. Unfortunatly I have done limited code writing, mostly creating macros, so Im slow. "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#6
|
|||
|
|||
|
I made the change to the line to show
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Owner\My Documents\PO" & n &".xls"_ where am I wrong? should the & n & be replaced with somthing? "FSt1" wrote: hi, you shouldn't be having errors if you made all the changes. did you? regards FSt1 "chris w" wrote: This sound exactly what I need. How can I use the code? I am getting syntax errors on the copy and paste. Unfortunatly I have done limited code writing, mostly creating macros, so Im slow. "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
Where do I start with the
MacCreatePO() command? "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
I have an Excel file that was created by someone else to address rejection
tags and I need to auto number in cell M2, i.e. Rejection Tag Number. Is this possible? "Ken" wrote: Where do I start with the MacCreatePO() command? "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
Your problem seems similar to mine - hope youcan help.
By default the number I type in a cell should stay the same once I enter it - e.g. when I type in 19 and press Enter the figure in the cell should still read 19. However it changes to 0.19. Do you know how to rectify this problem/ Douglas "FSt1" wrote: hi, you shouldn't be having errors if you made all the changes. did you? regards FSt1 "chris w" wrote: This sound exactly what I need. How can I use the code? I am getting syntax errors on the copy and paste. Unfortunatly I have done limited code writing, mostly creating macros, so Im slow. "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
Tools/ Options/ Edit
Uncheck: Fixed Decimal Places -- David Biddulph "Douglas" wrote in message ... ... By default the number I type in a cell should stay the same once I enter it - e.g. when I type in 19 and press Enter the figure in the cell should still read 19. However it changes to 0.19. Do you know how to rectify this problem/ |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
I recently ran across this post and it is helping me with my PO template.
The problem I'm having is that when the file autosaves and I open the saved file, I loss all my column width formatting. I know very little about programing, but I am wondering if it is the fileformat:= xlnormal that is resetting the column widths to the default settings. Please let me know. Thanks "FSt1" wrote: hi, I wrote this for someone a few months back. it was for pruchase Orders so you may have to make a few changes but it will do the same for you. Post back if you have problems. Sub MacCreatePO() 'Creates a new PO(purchase order) workbook, saving the created PO workbook with 'the PO number as part of the file name. Leaves the "template" PO workbook "as is". 'macCreatePO should be triggered from a button on the PO Template sheet. Dim n As Range Set n = Range("A1") ' the all important PO number. change if needed 'to a place in the PO template. Application.CutCopyMode = False 'clears the clipboard Range("A1:L25").Select 'change to accommodate the size of the PO template Selection.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll 'change the file path to where you want to save the PO workbook ActiveWorkbook.SaveAs Filename:="H:\CodeStuff\PO" & n & ".xls" _ FileFormat:= xlNormal, Password:="", WriteResPassword:="" _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close Cells(1, 1).Select 'go and park at cell A1 and wait MsgBox ("Purchase Order was saved as PO" & n & ".xls") n.Value = n.Value + 1 'add 1 to old PO number for new PO number 'change the place of the date to add. Macro puts today's date 'in but this can be edited. Range("I1") = Date 'clear the contents of the old PO to set up the next PO. clear surgically so 'to leave template in tact. more clearContents commands may be needed. 'Range("need areas to clear here").ClearContents ActiveWorkbook.Save 'save the cleared template workbook with new '(next) POnumber. 'Ready for next PO End Sub regards FSt1 "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Auto Numbering
Were you able to get this to work? It's exactly what I'm looking for but not
sure exactly where to place the code in Excel and what modifications I need to make. can you help? "Excel Man" wrote: I want my invoices to automatically go up in number. EX. Invoice 1, Invoice 2... I want this to happen every time I open my template to create a new invoice. I can't remember how to do this. Can anyone provide me with an answer? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Numbering Each Cell In A Column | Excel Worksheet Functions | |||
| Auto numbering of spreadsheets | Excel Discussion (Misc queries) | |||
| Auto page numbering for several worksheets | Excel Worksheet Functions | |||
| auto numbering | Excel Discussion (Misc queries) | |||
| AUTO PAGE NUMBERING DIFFERENTLY FOR A WORKBOOK | Excel Worksheet Functions | |||
Linear Mode
