Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

    On 2002-03-03 03:38, eddy wrote:
    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

    I do a similar thing generating worksheets for workers, where the worksheet uses an incremental number for the sheet number.

    There are several ways of doing this, the way I have found best is to use a hidden workbook which holds a serial number or number. This number is held in cell A1
    In my case the serial number file is fired up automatically when excel is fired up (like personal.xls).


    Sub SerialNumber()

    'Increment SerialNumber
    Range("[SerialNumber.xls]Sheet1!A1").Formula = Range("[SerialNumber.xls]Sheet1!A1").Value + 1
    Workbooks("SerialNumber.xls").Save

    SerialNumber = Range("[SerialNumber.xls]Sheet1!A1").Value
    'Replace CustomerName with code to reference your CustomerName
    Filename = SerialNumber & CustomerName & Format(Date, "dd-mm-yyyy")

    ' Then use Filename in your Save As code for the workbook.

    End Sub



  3. #3
    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 07:48, Anonymous wrote:
    On 2002-03-03 03:38, eddy wrote:
    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

    I do a similar thing generating worksheets for workers, where the worksheet uses an incremental number for the sheet number.

    There are several ways of doing this, the way I have found best is to use a hidden workbook which holds a serial number or number. This number is held in cell A1
    In my case the serial number file is fired up automatically when excel is fired up (like personal.xls).


    Sub SerialNumber()

    'Increment SerialNumber
    Range("[SerialNumber.xls]Sheet1!A1").Formula = Range("[SerialNumber.xls]Sheet1!A1").Value + 1
    Workbooks("SerialNumber.xls").Save

    SerialNumber = Range("[SerialNumber.xls]Sheet1!A1").Value
    'Replace CustomerName with code to reference your CustomerName
    Filename = SerialNumber & CustomerName & Format(Date, "dd-mm-yyyy")

    ' Then use Filename in your Save As code for the workbook.

    End Sub

    Whoops forgot to login )
    Regards,

    Gary Hewitt-Long

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ~Anne Troy

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
  •