Copying columns specified by a given set of conditions or criteria

[majkh85]

Really needing some assistance:-

I have a workbook with multiple sheets but what I am wantingto do is have all columns of one sheet automatically populate onto anothersheet if a certain word shows up in one of the columns.
For example: -


If the word April shows up in column D I want all the othercolumns in line with where april is (i.e. Row 1 and Row 4) to copy over toanother sheet.

COLUMN A COLUMNB COLUMN C COLUMN D

ROW1 Apples Oranges Bananas April
ROW 2 Bananas Apples Lemons February
ROW 3 Apricots Bananas Peaches August
ROW4 Bananas Lemons Apricots April



I know I can filter it and manually copy and paste but Ineed it to automatically populate if possible.

Thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you want to do this with formulas, then have a look through this thread where I suggested a method of doing a very similar thing & see if you can adapt it to your data.

 

[majkh85]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]NAME
[/TD]
[TD]SURNAME
[/TD]
[TD]GENDER
[/TD]
[TD]AGE
[/TD]
[TD]VACANCY
[/TD]
[TD]START DATE
[/TD]
[TD]PERIOD
[/TD]
[TD]AMOUNT
[/TD]
[TD]MONTH
[/TD]
[TD]SHOWING
[/TD]
[/TR]
[TR]
[TD]124
[/TD]
[TD]Jane
[/TD]
[TD]Doe
[/TD]
[TD]F
[/TD]
[TD]30
[/TD]
[TD]2536
[/TD]
[TD]10/10/10
[/TD]
[TD]17
[/TD]
[TD]$2
[/TD]
[TD]August
[/TD]
[TD]Open
[/TD]
[/TR]
[TR]
[TD]588
[/TD]
[TD]John
[/TD]
[TD]Doe
[/TD]
[TD]M
[/TD]
[TD]46
[/TD]
[TD]1579
[/TD]
[TD]13/1/11
[/TD]
[TD]16
[/TD]
[TD]$5
[/TD]
[TD]October
[/TD]
[TD]Open
[/TD]
[/TR]
[TR]
[TD]769
[/TD]
[TD]Bev
[/TD]
[TD]Beverly
[/TD]
[TD]F
[/TD]
[TD]78
[/TD]
[TD]5345
[/TD]
[TD]4/5/18
[/TD]
[TD]20
[/TD]
[TD]$9
[/TD]
[TD]August
[/TD]
[TD]Open
[/TD]
[/TR]
[TR]
[TD]222
[/TD]
[TD]Amanda
[/TD]
[TD]Snow
[/TD]
[TD]F
[/TD]
[TD]32
[/TD]
[TD]4258
[/TD]
[TD]6/8/03
[/TD]
[TD]66
[/TD]
[TD]$6
[/TD]
[TD]August
[/TD]
[TD]Open
[/TD]
[/TR]
</tbody>[/TABLE]
Hi Peter,

I have attempted to adapt it but it for some reason it is not copying over properly,

Above is a table of my sheets, your assistance would be greatly appreciated.

I would like for all columns to be automatically copied over to another sheet if the Month column shows "August"

 

[Peter_SSs]

I have attempted to adapt it but it for some reason it is not copying over properly,

Using the method in that linked thread it could be like this.

M2 copied down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	NAME	SURNAME	GENDER	AGE	VACANCY	START DATE	PERIOD	AMOUNT	MONTH	SHOWING		Helper
2	124	Jane	Doe	F	30	2536	10/10/2010	17	$2	August	Open		August|1
3	588	John	Doe	M	46	1579	13/01/2011	16	$5	October	Open		October|1
4	769	Bev	Beverly	F	78	5345	4/05/2018	20	$9	August	Open		August|2
5	222	Amanda	Snow	F	32	4258	6/08/2003	66	$6	August	Open		August|3
All Data

N2 copied down
B2 copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	ID	NAME	SURNAME	GENDER	AGE	VACANCY	START DATE	PERIOD	AMOUNT	MONTH	SHOWING
2	August	124	Jane	Doe	F	30	2536	40461	17	2	August	Open		2
3		769	Bev	Beverly	F	78	5345	43224	20	9	August	Open		4
4		222	Amanda	Snow	F	32	4258	37839	66	6	August	Open		5
5														#N/A
August