Macro to send data to word/Auto Number generation

[eddy]

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. 001<auto number>FredBloggs<Name from cell>12022002<date>
I would then like another macro within worksheet 2 to export the information
into a pre-prepared word document.
Thanks for the help.
Ted

 

[Guest]

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. 001<auto number>FredBloggs<Name from cell>12022002<date>
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

 

[gplhl]

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. 001<auto number>FredBloggs<Name from cell>12022002<date>
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 )

 

[Anne Troy]

Ooops. Same question here:

http://www.mrexcel.com/board/viewtopic.php?topic=1113&forum=2&1