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]

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.

 

[rickf19]

Is this correct ?

 

[kanadaaa]

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]

Thanks for the reference,
so I copy code from #16 into Module 1 Code box , save worksheet , then Developer -Macros- Run ?

 

[rickf19]

 

[kanadaaa]

 

[rickf19]

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

 

[kanadaaa]

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.

 

[rickf19]

I could email it across to you if that's allowed

 

[kanadaaa]

I sent you a PM so check it out.