what i need is a sign in template that prints out daily in landscape Each daily sheet must be 2 sided i need to print out 28 days worth at a time for the sign in book I need all the days dates to populate automatically after entering the first date Example sheet 1 both sides would have typed in friday 14 april 2023 auto populate then sheet 2 (day 2) all the way to sheet 28 (day 28) and having only 1 header per page each side
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
help needed with this sign in sheet
0
Discussion
Hi Graham,
John has provided you with a good option. I too would like to offer a possible solution but using vba and a macro to print the sheets. To do this I need a bit more information.
Do you need these sheets for M -> F only or do you also need Sat-Sun? Will these sheets be hole punched and placed into a binder? (this will affect page layout) Is there a particular reason why you have chosen to do this in 28-day blocks?
John has provided you with a good option. I too would like to offer a possible solution but using vba and a macro to print the sheets. To do this I need a bit more information.
Do you need these sheets for M -> F only or do you also need Sat-Sun? Will these sheets be hole punched and placed into a binder? (this will affect page layout) Is there a particular reason why you have chosen to do this in 28-day blocks?
WillieD24 (rep: 557) Apr 14, '23 at 2:29 pm
hi yes it is needing to have m-s a full 7 day week as we are open 24 hrs a day 7 days a week and i use the 28 day cycle only for convenience as i nly have to date print and place into folder once every 28 days at the moment we use a clip doard for them but going double sided a folder would be a better option
grahamscown (rep: 8) Apr 14, '23 at 4:17 pm
@Willie - good catch: using a ring binder is a very practical measure (and one I hadn't considered though it could probably catered for in my non_
-macro soluton).
-macro soluton).
John_Ru (rep: 6142) Apr 15, '23 at 1:39 am
@John,
When I first looked over Graham's sheet layout, the extra space (margin) at the top made me think the sheet would be hole punched for a binder. If Graham later decides to use my method and the sheets will be hole punched, then row 44 (and maybe row 43) will need to be deleted or the holes will fall in those rows.
Also, in my version the header info on the second page is referenced to the header on the first page. So, make changes to rows 1,2,3 and rows 23,24,25 are updated. And the print area is set to "A1:H44".
Cheers :-)
When I first looked over Graham's sheet layout, the extra space (margin) at the top made me think the sheet would be hole punched for a binder. If Graham later decides to use my method and the sheets will be hole punched, then row 44 (and maybe row 43) will need to be deleted or the holes will fall in those rows.
Also, in my version the header info on the second page is referenced to the header on the first page. So, make changes to rows 1,2,3 and rows 23,24,25 are updated. And the print area is set to "A1:H44".
Cheers :-)
WillieD24 (rep: 557) Apr 15, '23 at 8:17 am
@Willie - agreed but remember that in my solution the first 5 rows are the only place where there's top if page infomation. It just gets editted there'll
John_Ru (rep: 6142) Apr 15, '23 at 2:49 pm
Answers
0
Selected Answer
Graham
Given you want the same header information, I suggest you have that only once, at the start of your file. (I've done that in the attached revised file and corrected cell A3 to include "hard hats" which appeared in cell A49 etc). That way if you need to change the text (or column headers or formatting), you do it in one place.
The trick is then use ribbon Page Layout/ Print Titles and set Rows to repeat at top: to include those 4 rows- it now reads:
$1:$4Every page will start with whatever is in those rows (use Print Preview to see the headers appear on each page).
It's then just a matter of incremeting the dates then...
To do that, I named cell A5 as "StartDate" (in ribbon Formulas/Name Manager) and cell A6 formula:
= A5(and likewise for B5/B6) then I copied (only) your signatures rows down. The date from A5 then showed all the way down.
You seemed to be getting 18 signature lines per page (but that may depend on print margins) and wanted doubled sided so I changed the formula in cell A(5 + 2*18)= A41 to become:
=StartDate +1Now the second date rippled down to the end.
I did then similar for these days/ cells/ formula changes assuming 18 lines per page:
For Cell Changed formula to:
Day 3 A77 =StartDate+2
Day 4 A113 =StartDate+3
Day 5 A149 =StartDate+4
Day 6 A185 =StartDate+5
Day 7 A221 =StartDate+6so you get the first week of date changes.
I leave you to do these formula changes:
For Cell Change formula to:
Day 8 A257 =StartDate+7
Day 9 A293 =StartDate+8
Day 10 A329 =StartDate+9
Day 11 A365 =StartDate+10
Day 12 A401 =StartDate+11
Day 13 A437 =StartDate+12
Day 14 A473 =StartDate+13
Day 15 A509 =StartDate+14
Day 16 A545 =StartDate+15
Day 17 A581 =StartDate+16
Day 18 A617 =StartDate+17
Day 19 A653 =StartDate+18
Day 20 A689 =StartDate+19
Day 21 A725 =StartDate+20
Day 22 A761 =StartDate+21
Day 23 A797 =StartDate+22
Day 24 A833 =StartDate+23
Day 25 A869 =StartDate+24
Day 26 A905 =StartDate+25
Day 27 A941 =StartDate+26
Day 28 A977 =StartDate+27
Once you've done that, you've only to change cell A5 (and B5 perhaps- both shaded light blue for now) then print double-sided.
Hope this makes sense and works for you.- if so, please remember to mark this Answer as Selected.
1
Hi Graham,
Attached is my VBA version.
There is only one caveat - just like you have been doing, the printer must be set for 2-sided printing before you begin printing.
The macro has a message box reminder/check that the printer has been set to 2-sided. After selecting "Yes", the macro will will print the sheet 2-sided for the date shown, then increment the date by 1 and print, increment the date by 1 and print,...... for a total of 28 dates. After printing 28 sheets it sets the date to the next date to print so there is nothing you need to change the next time you need to print.
If you want to print more or less than 28 sheets just change the 28 in the following to how mant sheets you want.
For i = 1 To 28 ' print 28 timesIf you prefer John's solution better, that's OK. It's your choice.
Cheers :-)