Help with auto-generating reservation forms

**whoatethepies** · 01-31-2007, 09:14 PM

Hi guys,

I need help creating a macro to change a number of items in a spreadsheet (attached). Its a reservations list for a restaurant. For each day, we have 2 sheets, one for lunch, the other for dinner reservations (they're the same but for the "Lunch" or "Dinner" title..).

Firstly the booking reference numbers need to be updated each time. These are the numbers in the grey boxes (starts at 1000 in the attached file). Each one needs to be unique, and at the moment cell A3 has an hard coded integer, with all other cells incrementing by +1 from the previous cell to give it a unique number. I need to be able to update the number in A3 to equal the value in H27 +1 (i.e. A3=H27+1), each time, creating unique numbers for the reservation sheets on subsequent days.

However, whenever I've tried to do this myself it skips quite a few numbers in-between (instead of incrementing by 1, it does so by a couple of hundred!).

Secondly, I need to be able to increment the date. We generally print off a months worth of reservation sheets at a time, and each on needs to contain the date (for example, Friday 2nd March). Therefore I need some way of correctly incrementing the days (fri, sat..) and the date (2nd, 3rd..) EVERY OTHER time the reservation numbers are updated. This is because each day we need to print 2 sheets, one for lunch and one for dinner as I mentioned above. It'd also help if it could ammend the appropriate cell with "Lunch" if it contains "Dinner" and visa versa each time.

I know thats a bit long winded (sorry!), no idea if it can even be done but any help is much appreciated.

Hope that all makes sense,

Mark

**mrice** · 02-01-2007, 04:25 PM

No attachment???

**whoatethepies** · 02-01-2007, 06:47 PM

Odd, thought I attatched it.. Attached to this one.

**mrice** · 02-05-2007, 05:52 PM

I've come up with the attached - I hope that its close to what you need.

The code uses a user form to ask for the dates that you want to use - beginning and end. It then creates a new workbook with all the day/meal combinations on separate tabs. It uses your template by copying it onto each of the sheets. Therefore if you wish to change the number of tables, you just need to change the template.

It should deal with leap years - at least until 2100

The code uses a number of loops and select statements which keeps the overall number of lines down.

To print out the sheets, select print and then the option to print out the entire workbook.