Need to create duty list using VBA

jb · Oct 30, 2025

Hello Helpers,
I have an excel file which contains 3 sheets - list, duty_list and individual_duty.

list sheet contains serial no., code and name of employees. (column A, B and C).
This sheet contains a button - Generate slips.

This sheet contains a table F3 to I9. It contains code of employees who are going to be assigned some duties.

When the duty list will be generated, one page will have 4 employee duty list (in order to save pages). Thats why 4 codes are written in 1 line of table F3-I9.
There are 7 lines in this table because 26 employees are given duties.

duty_list sheet contains duties given to all the employees. Note that duties are given in 2 slots on some days and in 1 slot on some days.

individual_duty sheet contains format of duty list sheets to be generated.

When Generate slips button will be pressed from list sheet, it must generate a pdf which will contain 7 pages and total 26 slips (4 in one page).
And the individual slip should contain right tick mark symbol in the respective cell against a particular date and morning/afternoon slot whenever the employee is given duty. This part is done and you can see it in individual duty sheet.

When you press the generate slip button, it is generating 7 separate pdf.

My requirement is:
1. I want to generate single pdf.
2. If possible, last page is displaying error for last 2 duty sheets (because 26 employees gives 6 full page and last page only 2 sheets)
3. Can we avoid F3-I9 table because it is required to be typed manually.

vletm · Oct 31, 2025

What ... who generates ... Duty_List-sheet?

Duty_list-sheet:
>> C4 = 10:00 and D4 is eight hours before C4 ... okay?
... hmm? Individual_Duty-sheet shows that 02:00 is afternoon ... which is 14:00 ... hmm?

>> Could there be all days (from day to day) and each day has two slots?

Do only the duty list's values which are 1 show in ... Individual_Duty-sheet?

Could You show - what do You expect?
... instead of what do You have.

jb · Oct 31, 2025

vletm said:
What ... who generates ... Duty_List-sheet?

Duty_list-sheet:
>> C4 = 10:00 and D4 is eight hours before C4 ... okay?
... hmm? Individual_Duty-sheet shows that 02:00 is afternoon ... which is 14:00 ... hmm?

>> Could there be all days (from day to day) and each day has two slots?

Do only the duty list's values which are 1 show in ... Individual_Duty-sheet?

Could You show - what do You expect?
... instead of what do You have.

There can be one slot for some days. There can be 2 slot for some days. These data will be as per the duty_list sheet. This sheet is manual entry by super visor. List sheet is also manual entered by supervisor.

Do only the duty list's values which are 1 show in ... Individual_Duty-sheet? Yes.

Now, supervisor wants to handover the duty sheet to individual employee. So, he has written formula in individual_duty sheet where in one page he has accommodated 4 duty sheets in order to save pages.

One VBA code is written which will be executed upon clicking button available on list sheet. It is already working and generating 7 pdf in selected folder.

I expect:

Everything is working perfectly but in place of generating 7 different pdf, I want all duty sheets in one pdf.

To display 4 duty list in one page, there is one table F3-I9 in list sheet which is required to be typed manually. As per that table, 4 employee duty list is generated in each page. Is there any way to avoid typing this table?

jb · Oct 31, 2025

vletm said:
What ... who generates ... Duty_List-sheet?

Duty_list-sheet:
>> C4 = 10:00 and D4 is eight hours before C4 ... okay?
... hmm? Individual_Duty-sheet shows that 02:00 is afternoon ... which is 14:00 ... hmm?

>> Could there be all days (from day to day) and each day has two slots?

Do only the duty list's values which are 1 show in ... Individual_Duty-sheet?

Could You show - what do You expect?
... instead of what do You have.

I found the solution of generating single pdf in place of multiple pdf.

But the display format of my Individual_Duty sheet is not preserved in my output pdf.

Refer my revised excel file.

vletm · Oct 31, 2025

My question: Could there be ...
... seems there could be based Your writing.

Even Your the latest Individual_Duty-sheet looks same; four names.
... should guess something?

If I would start to do something then I would do this in my way...
... after, I could know - what do someone really need to get?

Something like this (not ready)
Duty has Your List and Duty_List.
#1 From left-top - supervisor could select ... what to write.
#2 select data/name cross to mark previous selection (as many times as need)
From those marks could create Your Individual_Duty PDF ... after You've shown - what it would look like?
... okay
Here my sample based Your answers.

jb · Saturday at 6:45 PM

vletm said:
My question: Could there be ...
... seems there could be based Your writing.

Even Your the latest Individual_Duty-sheet looks same; four names.
... should guess something?

If I would start to do something then I would do this in my way...
... after, I could know - what do someone really need to get?

Something like this (not ready)
Duty has Your List and Duty_List.
#1 From left-top - supervisor could select ... what to write.
#2 select data/name cross to mark previous selection (as many times as need)
From those marks could create Your Individual_Duty PDF ... after You've shown - what it would look like?
... okay
Here my sample based Your answers.

I really appreciate your efforts. But my revised sheet is in perfectly working condition as per my requirements except only one thing.

When my pdf is generated, it just have values fetched from duty sheet. My pdf format must be as per the individual_duty sheet.
Only that thing is missing.

How to copy values with all the formats in pdf? If you can help.

Today, I am attaching excel file and generated pdf both. You just check my generated pdf. Compare the format with individual_duty sheet.
You will immediately understand what i want to convey.

vletm · Saturday at 8:34 PM

my revised sheet is in perfectly working condition as per my requirements
... hmm?

You will immediately understand what i want to convey.
as You've asked