Thanks Claus, I'll give it a go.



Howard

Hi Claus,

set wbSource =ThisWorkbook

Worked very nicely!

Thanks.

Howard

Note that every copy you make using ThisWorkbook.SaveAs contains
macros,
which is not recommended as a 'good practice' in general!

<FWIW
Since you appear to be using an original xlsm as a template for making
quotes, I recommend you copy the quote sheet to a new workbook and work
with that so it's macro-free when opened by others. This approach
leaves the source file containing code being used as a 'master'
template for generating quotes.

Optionally, you could store all copies of the quote sheet in the source
file and output each new quote to PDF using SaveAsFixedFormat. (See any
post I've responded to with "pdf" in the subject line for examples of
the various ways to do this via code).

Optimally, store the quote 'template' in a separate file (or files if
more than 1) so you can 'Add' sheets to the master file based on a
preset template. This allows switching templates 'on-the-fly' if you
want to use the code in the master file as a "quotation" project
capable of creating 'quotes/sales orders/purchase orders' from a single
project. I have a few generic projects that do this, plus a proprietary
addin I made for a client that used (at the time) 18 different quote
sheets that were used by sales reps selling the client's products. The
quote could be copied as a Sales Order to get signed by the customer,
and/or copied as a Purchase Order to be submitted to my client.

I have my own generic version of this as a 'PointOfSale' addin ("POS")
which also stores customer info as an option if dealing with repeat
customers. I use this with clients that require some way to present a
quote or invoice 'in-the-field' or outside their accounting software.
The primary useage is for products that are available in multiple
configurations, meaning each 'item template' has sections containing
all possible options/configs and users (sales reps) just enter for
'Qty' of the desired option[s]. Printouts can (optionally) only include
the selected items/options.

Obviously all templates share the same 'structure' so code refs work
regardless of layout. Some also include current pricing so all calcs
happen automatically when a Qty is entered for each line item. All
include handling of sales taxes as 'Tax1' and 'Tax2' (option if VAT is
used).

Each original quote is stored in a 'period' workbook named with the
following formats...

"MyOrgName_yyyy_mm_Type.FileExtension" '//monthly
"MyOrgName_yyyy_mm#_Type.FileExtension" '//weekly per month
"MyOrgName_yyyy_Type.FileExtension" '//yearly

...where '_Type' is either "Quotes" or "Invoices", and "#" denotes the
week of the specified month.
</FWIW

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion