Returning values from 1 sheet based on a single value in another

[rickf19]

Hi all

I have a spreadsheet of data that has 6 columns B,H,I,J,L which I want to pull all the rows that have a specific value in COL E

EG

B = Period
H = Date
I = Detail
J = Amount
L= Ref

E = Code

I want to create a number of Tabs that will pull out the data in each of the B to L columns based on the value in Col E
I could just filter the data sheet on the values in Col E and copy/paste to each tab, but the data sheet is updated regularly and there are approx 45 different codes to create sheets for.


Any help gratefully received
Rick

 

[kanadaaa]

It overwrites the existing data on each sheet created by it. This is how it works.
So, if you have the row below in a month and run the macro...

1164672-2 Add Sheets Based on Value.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
4		Period	Code 1	TB Desc	Code 2	Description	Old TB Place	Date	Detail	Amount	Blank1	Trans Ref	Note	Headings
5		1	210701	Benefits Payable	11	Benefits	96	04/06/20	F REEKS Dec'd (KR761****) Returned February instalment	-17.49		CBP	07	21
Data

you get the following on the sheet for code 11:

1164672-2 Add Sheets Based on Value.xlsm
	A	B	C	D	E
1	Code2 =	11
2	Period	Date	Detail	Amount	Ref
3	1	06/04/2020	F REEKS Dec'd (KR761****) Returned February instalment	-17.49	CBP
Sheet1

But once the row in the first image is deleted, the sheet in the second image won't have the data in this row whenever you run the macro again.

 

[kanadaaa]

To make it clearer, the data once outputted remains to be there unless you delete it on sheet Data.
So if you need to preserve the data in the previous month, just run the macro (no need to do anything else).
If you don't, clear the existing data on sheet Data and then run the macro.

 

[rickf19]

OK
thanks again that puts my mind at ease

Rick