Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro to send data to word/Auto Number generation

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ..
    I have a workbook the first worksheet contains several line items (about 500
    lines). In ColA a quantity is entered.
    I then have a macro which moves all the items with a quantity in to the top
    of the list.
    I have cells in the first sheet which include a customer name and address.
    I would like to move certain selected cells such as Name / Address / Qtys /
    Prices / Description etc to Worksheet 2 and then
    save the file, this is the hard bit, or maybe not. I would like to auto
    generate a number for use in the filename every time
    the macro is run but also need to include the date and the customers name
    e.g. 001FredBloggs12022002
    I would then like another macro within worksheet 2 to export the information
    into a pre-prepared word document.
    Thanks for the help.
    Ted


  2. #2
    Guest

    Default

    My opinion only:

    1. Don't bother with the Word document part. Just create your invoice (or whatever) right in Excel. You can have your cust names, address, blah, blah, and have Sheet1 be your invoice sheet that does vlookups to your customer data sheet, and then vlookups to your products sheet. As for Word, if you must, use a mailmerge.

    2. Oops. I found it. Here's a bit of code that, every time you open the workbook, gives it a unique value (invoice number?).

    Private Sub Workbook_Open()
    With Range("A1")
    .NumberFormat = "00000"
    .Value = .Value + 1
    End With
    End Sub

    Sorry. That's all I've got.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cambridge England
    Posts
    521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  4. #4
    Guest

    Default

    Thanks thats great, I can now almost do what I want. Does anyone know how to produce a macro which will look at two cells e.g. A1 and B1, combine the two cell content to be the string for the saved filename. e.g cell A1 contains Smith&Co, cell B1 contains 0001 so the saved filename is Smith&Co0001.xls. The idea will be to have a macro button lablled "SAVE".

    Thanks Eddy

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-03 09:21, Anonymous wrote:
    Thanks thats great, I can now almost do what I want. Does anyone know how to produce a macro which will look at two cells e.g. A1 and B1, combine the two cell content to be the string for the saved filename. e.g cell A1 contains Smith&Co, cell B1 contains 0001 so the saved filename is Smith&Co0001.xls. The idea will be to have a macro button lablled "SAVE".

    Thanks Eddy

    ActiveWorkbook.SaveAs Filename:="C:" & Range("A1").Value & Range("B1").Value

    Change C: to whichever directory you want to save in.


    Regards,

    Gary Hewitt-Long

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
  •