forumulas in macros?

[bartlett]

is there any way in my macro to add one to a number. What i want to do is create my own invoice numbers. So when I get done with an invoice I can start a new invoice with a new invoice number automatically.

 

[NateO]

Not sure I'm following, but if you want a separate worksheet for every invoice

Sub Invoicer()    
n = [a1].Value + 1
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "Invoice_" & n
[a1] = n
End Sub

Where cell a1 houses your invoice number. The only reason for copying the active sheet is to grab the formatting you can clear the contents quickly via code, e.g.,

[a7].clearcontents

 

[bartlett]

Thanks all-
NateO-i'll try it
thanks

 

[NateO]

Yes and if it's not what you had in mind, post back, I was struggling a bit to picture what you had in mind...

 

[bartlett]

NateO
couldnt get it to work. I'll try to explain better what I am trying to do.

I created a simple invoice on a worksheet. I then fill in the information on the worksheet, print the worksheet, press a button, which then puts the name on the invoice, contract number and price on to another worksheet which i call a log. What I need to add is a way to press the button which then "logs" the info and assigns a new contract number. Hence contract+1. If there was also a way to clear the contents at the same time I press the button and assign a contract number would be great. Thanks

 

[Joe Was]

Try this code:

Private Sub Workbook_Open()
'By Joe Was, 2/2002.
'Place this code in the "ThisWorkbook" Module
'to increment on the opening of the workbook.

'Sub Worksheet_Activate()
'Use the "Activate" Sub in a Sheet Module
'to increment on a sheet selection.

[A3].Value = [A3] + 1
ActiveWorkbook.Save
MsgBox "Each time this sheet is opened" & Chr(13) & "the starting number is increased." _
& Chr(13) & "And this blank template is saved" & Chr(13) & _
"with the new starting number!" & Chr(13) & Chr(13) & _
"Please, save any added data" & Chr(13) & "with a new file Name!" & Chr(13) & Chr(13) & _
"To preserve system integrity" & Chr(13) & "follow these directions!"
End Sub

The MsgBox will tell you what you need to do.
This saves your blank master with a new invoice number in cell A3. Before you add any data you should save the new workbook with a new name. That way the master will keep track of the last invoice number for you. The pattern of your starting invoice number will set the pattern for the incerement.
Hope this helps. JSW
This message was edited by Joe Was on 2002-04-26 08:11

 

[bartlett]

Joe Was,
I tried your macro-couldn't get it to work. Is there any way to put a formula in my current macro where:

"A1" (cell with number)="A2" (logged invoice)+1.

And have it execute when the macro is executed?

 

[Joe Was]

You must have a starting value in cell A3 for this code to work, Like: 100001

The above code worked in both 2000 and XP.

Did you catch the two Subs at the start of the code, you only use one! The firs one will auto-run the code when you open the workbook and the second version will run the code when you select the sheet. JSW

 

[Joe Was]

The key code is:
[A3].Value = [A3] + 1
where, [A3] is the address of the cell which contains your starting invoice number. Your starting value should not contain any text only numbers. JSW

 

[bartlett]

Joe Was
your last "key code" is exactly what i needed.
Thanks,
If I could ask another question-is there any way to add to the macro a "print" command. and also just to print one page of the worksheet.
Thanks again

 

[Joe Was]

Excel will print the active sheet using the preset "SetPrintArea" and the "PageSetUp" for that sheet, each sheet in Excel has its own sheet print setup.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

If the active sheet is not the sheet you want to print add this code, before the PrintOut code line:

Sheets("SheetWhatEverYouWant").Select

Hope this helps. JSW