Copy Paste Range based on Column Header Match

[marillionnut]

Hi, I've trying to solve a problem of copying data from one sheet to another based on the column header dates matching. The workbook is used to collect data over a week (sheet1) so as to produce weekly/monthly/annual data for a weekly report. At the start of each week the correct date is selected in sheet1 D3 and this changes the header dates Sheet1 D9:K9 and also feeds into the report and resulting charts etc (I am in the Middle East, so start of the week is Sunday...)

To make life easier for the team using the workbook all I need is for them to record the data over a week and then using a macro via a button, move that data into the corresponding dated columns in sheet2. When using simple offset code, it doesn't prevent the data being entered into the wrong dates, ie there is no check on matching headers - I need it only to go into the dates shown in sheet1 D9:K9

Many thanks,

Martin

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1				Select Week Start Date
2							Week No	Sunday	Saturday	Year	Date of First Whole Week
3	Week Starting			17-Jan-21			3	17 Jan	23 Jan 21	2021	03-Jan-21
4
5	Daily Log - for use throughout the week
6
7
8
9				Sat 16 Jan	Sun 17 Jan	Mon 18 Jan	Tue 19 Jan	Wed 20 Jan	Thu 21 Jan	Fri 22 Jan	Sat 23 Jan	Totals
10	HSE		Incident LTI									0
11			Incident Non -LTI									0
12			Near Miss									0
13	Incidents		Fire									0
14			HAZMATS									0
15			Rescue from Height									0
16			Rescue Confined Space									0
17			Road Traffic Collison									0
18			AFD False Alarm									0
19			Medical/Joint									0
20			Special Service Call									0
21			Standby Duties									0
22			Other									0
Sheet1

Cell Formulas
Range	Formula
G3	G3	=WEEKNUM(D3,2)
H3	H3	=MAX(DATE(J3,1,1),DATE(J3,1,7)-WEEKDAY(DATE(J3,1,1),2)+(G3-1)*7+1)
I3	I3	=MIN(DATE(J3+1,1,0),DATE(J3,1,7)-WEEKDAY(DATE(J3,1,1),2)+G3*7)
J3	J3	=YEAR(K3)
D9	D9	=H3-1
E9	E9	=H3
F9:K9	F9	=E9+1
L10:L22	L10	=SUM(D10:K10)

Cells with Data Validation
Cell	Allow	Criteria
D3	List	=List!$A$1:$A$52

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1		Full Date	Sun 03 Jan	Mon 04 Jan	Tue 05 Jan	Wed 06 Jan	Thu 07 Jan	Fri 08 Jan	Sat 09 Jan	Sun 10 Jan	Mon 11 Jan	Tue 12 Jan	Wed 13 Jan	Thu 14 Jan	Fri 15 Jan	Sat 16 Jan	Sun 17 Jan	Mon 18 Jan	Tue 19 Jan	Wed 20 Jan	Thu 21 Jan	Fri 22 Jan	Sat 23 Jan	Sun 24 Jan	Mon 25 Jan	Tue 26 Jan	Wed 27 Jan	Thu 28 Jan	Fri 29 Jan	Sat 30 Jan	Sun 31 Jan	Mon 01 Feb	Tue 02 Feb	Wed 03 Feb	Thu 04 Feb	Fri 05 Feb	Sat 06 Feb	Sun 07 Feb	Mon 08 Feb	Tue 09 Feb	Wed 10 Feb	Thu 11 Feb	Fri 12 Feb	Sat 13 Feb	Sun 14 Feb	Mon 15 Feb	Tue 16 Feb	Wed 17 Feb	Thu 18 Feb	Fri 19 Feb	Sat 20 Feb	Sun 21 Feb	Mon 22 Feb	Tue 23 Feb
2	HSE	Incident LTI
3		Incident Non -LTI
4		Near Miss
5	Incidents	Fire
6		HAZMATS
7		Rescue from Height
8		Rescue Confined Space																										1
9		Road Traffic Collison
10		AFD False Alarm
11		Medical/Joint
12		Special Service Call
13		Stand by Duties
14		Other
Sheet2

Cell Formulas
Range	Formula
D1:BB1	D1	=C1+1

 

[LexCorp]

Martin, did you have any luck with this? Looking to do something very similar myself, but struggling to find due to 1. How to explain it as well as you did or 2. how to search for it efficiently. Been trying for days!! Hoping you had some luck. Mine is similar in the sense we drop in a bunch of data into one sheet, then I want it to spit out the data for that day into a daily numbers table on another sheet. Want the macro button to do it to stop someone breaking the spreadsheet.

 

[marillionnut]

Martin, did you have any luck with this? Looking to do something very similar myself, but struggling to find due to 1. How to explain it as well as you did or 2. how to search for it efficiently. Been trying for days!! Hoping you had some luck. Mine is similar in the sense we drop in a bunch of data into one sheet, then I want it to spit out the data for that day into a daily numbers table on another sheet. Want the macro button to do it to stop someone breaking the spreadsheet.

Hi, apologies for the delay in reply - haven't had a need to log onto the site for a while. I had some great help from a member (@Carim) on OzGrid and worked a treat.

Copy and Paste between sheets to match dates in headers - OzGrid Free Excel/VBA Help Forum

Hi, I have two sheets. Sheet1 has 8 columns, column A is the items being reported on, columns B to H are the headers; these are the dates of the calendar week (Sun to Sat). The days of the week change at the start of each week when the relevant start…

www.ozgrid.com