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]

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.

 

[eddy]

[No message]

 

[Guest]

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

 

[gplhl]

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.