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

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
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

Active Member
Joined
Dec 29, 2019
Messages
343
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
4PeriodCode 1TB DescCode 2DescriptionOld TB PlaceDateDetailAmountBlank1Trans RefNoteHeadings
51210701Benefits Payable11Benefits9604/06/20F REEKS Dec'd (KR761****) Returned February instalment-17.49CBP0721
Data

you get the following on the sheet for code 11:
1164672-2 Add Sheets Based on Value.xlsm
ABCDE
1Code2 =11
2PeriodDateDetailAmountRef
3106/04/2020F REEKS Dec'd (KR761****) Returned February instalment-17.49CBP
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
343
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,530
Messages
5,636,857
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top