Hello all,

I have a small transport company and i have a master overview of all shipments i expect. Now i've created a unloadingdocument, which i want to populate automatically with the data from the overview, so i want to select which lines i want to add to this unloadingdocument(perhaps via radio button, check button and then an execute button?)


I've tried online tutorials about macro's and vba but i can't make it work.
I've colorcoded where i want which information to be added on the template, everything that is white is for me to fill in when the goods arrive.


Many thanks in advance and please don't hesitate if you have more answers




kind regards

Attached Files

	Master Overview.xlsx (236.9 KB, 13 views)
	UNLOADDOC Empty.xlsx (12.4 KB, 13 views)

Personally, I would try using your Master Overview as "database" and create the template in Word.
It should not be too complicated using Mail Merge to extract the information.
Have a look at our Word forum and the Mail merge section to get a first idea about what can be achieved and if it suits your needs.
If you want t go that way, Macropod and the MSWord "gurus" will be of great help.

BTW in Excel you should always avoid using merged cells. They are nothing but trouble. For formatting purposes, " center across selection" is much better

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Would double-clicking on a line and having it highlight a certain color be an acceptable way of selecting what you want to add to the UNLOADDOC sheet ?

I think you can easily make it with a single workbook.

From where in your Master Overview workbook you want to read data into colored areas in UNLOADDOC?

Into 'Master Overview'.xlsx, add a sheet where you can register shipments (the table must contain an unique code for shipment);

Into table from where you want to read data, add a column for shipment code, and make fields in this column Data Validation Lists, so you can select the shipment for table row. NB! You must have all this data in single table!

Add a report sheet p.e. UNLOADDOC or whatever you want to name it. Design some cell to select shipment code. When shipment code is selected, all info available in data entry sheet is read into report (i.e. the report is filled). To get right rows into report, you need a helper column, where rows are numbered, and another one where entries with shipment code selected on report sheet are numbered. On report sheet you calculate the report row number, check that it is <= as max entry number with selected shipment code on data sheed, when yes, then find the row number with same entry number as report row number (SUMIFS() is your friend here), and read according info from data sheet into report field. When you need to fill some additional data before sending the report out, then enter this info into Shipments table and read from there into report.

When you want to send the report as Excel file, create an empty excel file, copy all datarange on report sheet, and use Paste Special to copy values, formats, and column widths (3 copy operations, in such way you avoid coping formulas which don't work in other Excel file, but preserve the current look of report).

When you want to send the report as printed document, then print it as pdf (install some free pdf-writer like CutePDF int your computer for this).

When you get this report back with some additional info filled in, and you want to keep this info in your Excel database, then have according fields in Shipments table (Unloaded by? Comments?) or in your data entry table (Present), and fill in the info from returned report.

When you want to send another shipment out, simply select on report sheet the code for this another shipment. It's all!

Additional recommendations:
Define your tables as Tables (Insert > Table) - when you add new records into table, all formulas, formats, data validations, etc. are added automatically for new rows (formats, formulas, or data validations must be same for whole column for this feature to work);
Add tables where you can register customers and transporters, and use data validation lists to select them into your data entry table - so you avoid typing errors which may affect your data integrity.