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
341
Office Version
  1. 365
Platform
  1. Windows
You pasted the code into a sheet module, not a standard module.
The code must be in a module in Modules in the Project Explorer, not the Microsoft Excel Objects.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Is this correct ?
 

Attachments

  • Screenshot 2021-03-16 110009.png
    Screenshot 2021-03-16 110009.png
    198.1 KB · Views: 2

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
No, my code in #16 has 3 procedures but it's one as a whole.
The entire code in #16 must be in ONE STANDARD MODULE and there must not be any fragment of it in any kind of other modules.
If you're not familiar with module types and procedures, you might want to see VBA Code Modules & Event Procedures for Sheet & ThisWorkbook.
What's called "code modules" on this website is what's generally called "standard modules".
 

rickf19

New Member
Joined
Aug 30, 2019
Messages
49
Thanks for the reference,
so I copy code from #16 into Module 1 Code box , save worksheet , then Developer -Macros- Run ?
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
Capture2.PNG


Capture3.PNG
 

Attachments

  • Capture3.PNG
    Capture3.PNG
    14.4 KB · Views: 1

rickf19

New Member
Joined
Aug 30, 2019
Messages
49

ADVERTISEMENT

Thanks again for your patience
Did as laid out above and ran macro result as below


Screenshot 2021-03-16 114636.png
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
My code names new sheets after code2 in column E.
So the error you're going through occurs when strings called "code2" are illegitimate as worksheet names.
Can you maybe share your workbook in some way?
It would take too much time without the actual workbook for debugging because I'd have to ask you for pieces of information I need one by one.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
I sent you a PM so check it out.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,395
Messages
5,636,054
Members
416,894
Latest member
Hari1992

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