Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: forumulas in macros?

  1. #11
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure I'm following, but if you want a separate worksheet for every invoice
    Code:
    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.,

    Code:
    [a7].clearcontents

  2. #12

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks all-
    NateO-i'll try it
    thanks

  3. #13
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...


  4. #14

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #15
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #16

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  7. #17
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #18
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #19

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #20
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •