VBA assistance for conditional copy and paste

[Peter Muller]

Hello VBA Experts,

I really do need your help!

I realise that my thread title did not indicate that I require VBA assistance, hence my re-post from Sun 3rd, with a new thread title.


Hello Experts,

I please need your assistance again. I have a problem, and have absolutely no idea how to go about resolving it. Please help.

• Each task in the "Task_Table" is either required on a specific day (Mon-Fri), or every day.
• When I activate the macro, today's day / the current day is used to identify which tasks must be copied and pasted in the input document which is linked to the pivot, with the daily tasks also copied from the 'Task_Table and pasted into the same input document.
• If there are 10 daily tasks, and 5 for a Mon, then running the macro and selecting "Mon", 15 tasks must be copied from the Tasks_Table and pasted into the input doc, and if 3 tasks on a Tue, then 13 tasks to be pasted into the input doc when "Tue" is selected.
• Each Day's tasks must be pasted below the last task of the 'previous day'.
• The input doc is in columns B:M, and the copied info is to be pasted into col D. The cell address where I select the relevant day for the macro is in "O2"

Task_Table			Input doc linked to Pivot - Columns B-M
Task	Frequency		Description 'Column D'		Mon
Reconcilliation	Daily		Reconcilliation
Replenish cash	Wed		Age Analysis - A-F
Age Analysis - A-F	Mon		Bank Recons
Age Analysis - G-L	Tue		Updated Skills Matrix
Age Analysis - M-Q	Wed		TNA, submitted
Age Analysis - R-Z	Thu		T scheduled & Monitored
Supplier Recons - A-F	Tue		Registers monitored
Supplier Recons - G-L	Wed		Late & Absent actioned
Supplier Recons - M-Q	Thu
Supplier Recons - R-Z	Fri
Bank Recons	Daily
Updated Skills Matrix	Daily
TNA, submitted	Mon
T scheduled & Monitored	Daily
Registers monitored	Daily
Late & Absent actioned	Daily

 

[sijpie]

This is not difficult to do, Peter, but I want to clarify a few things before I set to code it:

1. You say the input is in columns B:M. That is 12 columns. Your example only shows 2 columns.
2. Column D is in this range B:M, how is that?
3. When is the macro run? Always on today's dat or sometimes in advance (in preparation for tomorrow for instance)?
4. The list is to be copied below the last task of previous day. Does the user remove the items he has completed? Do you want to compact the list to remove any blank lines?

Please answer these

 

[Peter Muller]

This is not difficult to do, Peter, but I want to clarify a few things before I set to code it:

1. You say the input is in columns B:M. That is 12 columns. Your example only shows 2 columns.
2. Column D is in this range B:M, how is that?
3. When is the macro run? Always on today's dat or sometimes in advance (in preparation for tomorrow for instance)?
4. The list is to be copied below the last task of previous day. Does the user remove the items he has completed? Do you want to compact the list to remove any blank lines?

Please answer these

Good evening,

Thank you so much for assisting me.

Column D is the ‘lookup value’; hence, all the other columns are dependent on it. Example, Col B is the ‘Project Name”, which is looked up in/from a separate table based on Col D, and Col C is the category looked up from another table, also dependent on Col D, etc.

The info to be copied and pasted into Col D can be placed in the 1st col of the range, i.e., Col B, with the intended Columns B & C moved to the right (to C & D) – my only reason is aesthetics, and for logical flow.

Ideally, the macro is run at the start of day, Mon – Fri, but the day the macro uses to populate Col D, must be selected by the user typing the day in Col O2. Col O2 is formatted to ‘custom’ and ‘ddd’

The user does not remove the completed tasks, but only indicate with “Y” in Col F, if the task is completed.

 

[sijpie]

Is it possible for you to post a (sanitised if necessary) copy of the workbook in dropbox or similar? If difficult to sanitise, then you can let me know the location of the workbook in a private message. It will hel;p me understand better what you are trying to explain.

 

[Peter Muller]

Is it possible for you to post a (sanitised if necessary) copy of the workbook in dropbox or similar? If difficult to sanitise, then you can let me know the location of the workbook in a private message. It will hel;p me understand better what you are trying to explain.

	A	B	C	D	E	F	G	H	I
1
2									Tue
3		Project	Category	Description	Date	Completed	Weighting	Score	Progress
4		Finance	Petty Cash	Reconciliation	Tue 12/05	Y	5	5	100%
5		Finance	Petty Cash	Replenish cash	Tue 12/05	Y	3	3	100%
6

Macro from Info in 'I2' (deleted some of the columns, reducing the range) and Task Table

Col B & C is looked up from Task Table (below) based on the contents pasted into Col D

Col D is the results from the macro

Col E is the Date relating to Col D & I2

Col F is where the user enters "Y", otherwise it defaults to “N”

Col G - H is determined based on Col D, from Task Table

Col I calculated

		P	Q	R	S	T
1		Task Table
2
3		Task	Frequency	Weight	Project	Category
4		Reconcilliation	Daily	5	Finance	Petty Cash
5		Replenish cash	Tue	3	Finance	Petty Cash
6		Age Analysis - A-F	Tue	3	Finance	Debtors

 

[sijpie]

OK, clear now. I'll see what I can do

 

[Peter Muller]

OK, clear now. I'll see what I can do

Thank you

 

[sijpie]

Another question: Say that daily task Reconciliation was not completed on Monday. When you run the macro on Tuesday, do you want to add a new line Reconciliation, or leave the one unfinished open with adding a new one? (else you would have two open tasks Reconciliation)

 

[Peter Muller]

Another question: Say that daily task Reconciliation was not completed on Monday. When you run the macro on Tuesday, do you want to add a new line Reconciliation, or leave the one unfinished open with adding a new one? (else you would have two open tasks Reconciliation)

I am unsure of exactly what you are asking, but the intention is that Mon’s tasks remain, reflecting the ‘results’ of the day , Tue is added below that, wed below Tue, etc.

The idea is to eventually graph the performance trend for the employee.

 

[sijpie]

OK. Another question:
You use the cell I2 for day input. Is that important, or could the macro just use the current date?
problem is that I2 is a day, without a date. Yet in column E you use the date.

1. So I could use today's date, or use the next date following from the last date in the table (taking account of weekends).
2. Or if you want to stick to I2, I can calculate the date checking if today or tomorrow is the Tue entered in I2.

which option do you want?