Putting together a print out page from information in workbook.

Perhaps something like this?

To test run macro "AddAll". As I've got no printer at the moment I've not added the print part, that you will have to do.

Rerunning the macro will clear the "Print_sheet" before the next run starts. What is missing at the moment is a macro that loops through sheets "monday" to "friday" and clears previous orders. Probably best to do this as a "stand alone". Will do this later as I'm a bit pressed for time now.

Alf

Hello Alf,

Many thanks for your prompt reply and for spending you time to help me out. I appreciate it very much.

I am beginning to realise that what I have asked for is not that easy, but what you have sent me is exactly what I was trying to do.

I am not sure that clients would know how to run macros, so I may have to ask them to email back their order and I can run it and send them the print copy. This way I can also clear it prior to sending it out.

I forgot to include a space for them to put their name and address but I guess I will try and work that out on my own if I can.

In any event, I am a lot closer to completing this than I was this morning.

Many thanks once again and if you can find the time for the loop that would be incredible.

You are welcome and thanks for feedback.

The clearing loop could be something like this:

Please Login or Register  to view this content.

This macro will loop through sheets "Monday" to "Friday" and clear the order range and activating "Monday" sheet before it stops.

As I've not tested this macro as much as I normally do, could you please do a bit of testing like ordering several dinners on the same day or whatever you can think of. As I'm going off to Gothenburg in the morning my wife has a list of things to do for me and I'm sorry to say that trouble shooting excel macros is not on that list you must do a bit of testing.

There were some problem with the menue layout as some rows are higer than others and excel may sometimes be a bit picky about coping from one formate to another but I think i solved that.

I forgot to include a space for them to put their name and address

Perhaps you could add an extra page to your file where you have you customers name and address adding a bit of extra information like the season of ???? vegetables are aproaching and as we use as much homegrovn as possible we woud like to recommend ????

Or latest reserch shows that ????? Tings that could be of interest to your customers.

Do your costomers order for a full week every time or do they just pick some days? Since there are sum formulas in every sheet "Monday" to "Friday" macro could check A19, A33 and A66 and if all these are 0 on a specific day macro could skip this day or that particular activity i.e. if A19 was 0 on sheet "Monday" then the block "Monday Snacks" would not be copied to the "Print_sheet".



Alf

Hi Alf,

Firstly, I hope you enjoyed Gothenburg. Wife's are like that and very good at handing out lists. Mine does it all the time.

Ok, I ran the clearing macro several times and it works. So thank you for that.

I inserted a new page and put on the information that I required. Name, address etc.

One thing though. When I locked all the cells, except the column the clients put the quantity in the macro doesn't run. Oh well, I can live with that but I wonder how many people will try and change the other columns now.

To answer your question, the client is put on a weekly calorie controlled eating plan by a dietitian and so all orders for us are weekly. We cook and deliver a weeks worth of food which equates to quite a few meals every week. This is the Gold Coast and it seems like everybody is on some form of diet or other so we slotted into that market very well. Business is growing very fast and we are already looking for larger premises.

I have sent the completed form out to about five clients to get their feedback on it and if they find it confusing or not. I will let you know how that works out.

Once again, my sincere thanks for taking the time on this. You have helped us out enormously.

Best regards,

Alan.

Well picked up humdingaling, because I didn't even though I wrote it. I have mentioned this to my wife, (the chef) and she has told me to remove the choice of Thai or Japanese style as she will use them on alternate weeks. The clients actually like both so its all good.

Hi Alan

Did you ever read books about the fictious lawyer Rumpole written by John Mortimer? Rumpole calls his wife (when she is not around) "She Who Must Be Obeyed"

Oh well enough of pleasantries back to work!

In your original file sheets weekdays cells C26 and C31 should perhaps be centered see cells D26 and D31.

Sum formula in D66 is "=SUM(D56:D65)" should that not be "=SUM(D37:D65)"?

Since the macro uses autofilter to hide cells in column A where value is blank or zero this filtering process will not work when cells are locked. One workaround would be to let macro ask for psssword and you type it in or macro contains password to unlock cells / worksheet and macro run and at the last stage the file is locked with the password.

Or instead of using an Excel file for your customer to fill in perhaps it would be safer to convert the excel file to a pdf file for your customer to fill in their orders and then import the pdf table back to excel?

Don't know if I got any more idas at the moment.

Alf

Ps Thanks for feedback and rep

Hey Alf,

I have thought about your suggestion regarding the password macro and have come up with this plan, (comments welcome )

I will delete the print out sheet from the clients file. I can lock all the cells except where the client is required to insert quantities and other information. (Names, Addresses etc)

The client will send the file back to my wife.

I will then put their information in my spreadsheet and run the macro.

I can then convert the print out sheet to a PDF and return it to the customer as well a receipt as proof of purchase.

I think that about cracks it. What do you think?

Mate, a million thanks for seeing this through with me. If you ever come to Australia you must come and stay with us on the Gold Coast. The fishing, beaches, scuba diving etc are superb.

Cheers,

Alan.

Hi Alan
First of all thank you for your kind invitation and let me say in return should you come to Stockholm / Gothenburg please get in touch and I would really like to show you the sights of these cities.

I do like your setup idea. After deleting print out sheet you could also save the file as an xlsx (no macro) file so this could then be your client template file.

Perhaps you should also consider a folder structure. If 100 clients are ordering food for 30 weeks/year that adds up to 3000 files so a logical folder structure should make it easy to extract information.

Do you wish to find what your clients bought last year from “May” to “July”. Choosing the right file naming structure will make it easy to find answers like this.

As Excel is a flexible tool it’s still “jack of all trades but a master of none” so as business expands you may need a proper database program.

As for putting information into master sheet and run macro there are several options. The manual way takes time and is boring.

Alternative is macro opens clients order file loops through all sheets and copies values that are pasted to the different week day sheet in master file then macro is run and “print_sheet” is saved as a pdf file.

Or macro opens clients order file adds all weekday sheets to an array and array is copied to master file (containing only print sheet and macro). Macro starts by “unlocking” all week day sheets and then “condenses” orders to print sheet before the pdf file is created.

Have you consider any changes to the original macro?

I was wondering if macro should check cell D69 first on every sheet. If that value is 0 the macro should skip that day. Then macro should check cell D19, D33 and D65 before these blocks are condensed and copied to the print sheet. If any of the cell values are 0 that block should be skipped. I think that would make the receipt of purchase look more “professional”.

Cheers

Alf

Hi Alan

Thanks for your most interesting and informative PM. I’m really impressed what the two of you have achieved. Will reply to that later tonight.

A macro that loops through all pages in an “order form” copies and pastes information to relevant pages in a “Master” file is really a “piece of cake” but let’s wait a bit with that.

condense the client order information into something my wife can make sense of, so she knows what to cook and in what quantities.

Neither did I. Perhaps we can put this on “hold” for the time being untill we / you can decide where this information and at which stage in the process this could be captured. At the moment there is no mention of time i.e. you get an order but when should the food be delivered.

I have to say that I am far happier having deleted the print page and locking the workbook

So let me stop tinkering and let you start working with what you got at the moment.

We also have our first ten clients, and it hasn't even started yet,

Lovely I'll keep fingers crossed.

Cheers,

Alf

Ps If you think it could be of use to you I could write a short (hopefully) document explaining the macro and the macro commands.