VBA - copying range to different sheets based on cell values

[naveen_jaina]

I am trying to think of a way to copy a range of data from one sheet to another, based on the common value (ID) within the columns in both sheets. Although the attached image shows a small amount of data, I'm dealing with multiple sheets and a large number of rows, so prefer a VBA coding to get the output. What's the best method to approach such data manipulation? Does the loop work best in this kind of situation?

**Sorry, I haven't been able to use xl2bb to generate mini-sheet, that's why uploaded the image here again. **

Thank you!!!

 

[earthworm]

Share the sample sheet along with result for easy understanding about your requirement

 

[naveen_jaina]

Share the sample sheet along with result for easy understanding about your requirement

I think the xl2bb worked this time. Here's the sample data.

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		Table 1 in Sheet 1					Input
3	#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	1	Y	515	515	515	515			515	515	515	515	515
5	2	800	830	515	515	515			515	515	515	515	515
6	3			900	900	730	615	615			730	730	730	Y	615
7
8
9			#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
10			1
11	Table 2 in Sheet 1
12
13
14			2
15
16
17
18			3
19
20
21
22
23
24			#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
25			1	Y	515	515	515	515			515	515	515	515	515
26	Output Table 2 in Sheet 1
27
28
29			2	830	830	515	515	515			515	515	515	515	515
30
31
32
33			3			900	900	730	615	615			730	730	730	Y	615
34
35
36
Sheet1

 

[earthworm]

Try this . Now the whole table will work if you input Table serial number without gap if you want.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		Table 1 in Sheet 1					Input
3	#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	1	Y	515	515	515	515			515	515	515	515	515
5	2	800	830	515	515	515			515	515	515	515	515
6	3			900	900	730	615	615			730	730	730	Y	615
7
8
9			#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
10			1
11	Table 2 in Sheet 1
12
13
14			2
15
16
17
18			3
19
20
21
22
23
24			#	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
25			1	Y	515	515	515	515			515	515	515	515	515
26	Output Table 2 in Sheet 1
27
28
29			2	800	830	515	515	515			515	515	515	515	515
30
31
32
33			3			900	900	730	615	615			730	730	730	Y	615
34
35
36
Sheet1

Cell Formulas
Range	Formula
D25:Q36	D25	=IFERROR(IF(VLOOKUP($C25,$A$4:$O$6,COLUMNS($C$24:D$24),0)=0,"",VLOOKUP($C25,$A$4:$O$6,COLUMNS($C$24:D$24),0)),"")