Extracting info from master sheet to multiple sheets in same workbook

[Warrior1975]

Hello. I am using Office 365 and I am trying to figure out how to extract information from my master sheet and have it populate in other sheets in the same workbook. I would like to do it automatically as the master sheet is updated and it has to be for a certain criteria.

My master sheet has the following fields:

[TABLE="width: 5094"]
<colgroup><col><col><col><col span="25"><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col span="2"><col><col><col><col span="23"></colgroup><tbody>[TR]
[TD]Supplier [/TD]
[TD]Exam Date[/TD]
[TD]Exam Posted Date[/TD]
[TD]Exam Completed Date[/TD]
[TD]Exam Deleted Date[/TD]
[TD]Exam Status[/TD]
[TD]Exam Issue Id[/TD]
[TD]First Name[/TD]
[TD]Middle Name[/TD]
[TD]Last Name[/TD]
[TD]Suffix[/TD]
[TD]DOB[/TD]
[TD]Sex[/TD]
[TD]Height Inches[/TD]
[TD]Weight Lbs[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Home Phone[/TD]
[TD]Work Phone[/TD]
[TD]Cell Phone[/TD]
[TD]Email[/TD]
[TD]Contact Person[/TD]
[TD]Contact Person Phone[/TD]
[TD]Primary Care Physician Name[/TD]
[TD]Primary Care Physician Phone[/TD]
[TD]Has Insurance[/TD]
[TD]Medicare Number[/TD]
[TD]Medicaid Plan[/TD]
[TD]Medicaid Number[/TD]
[TD]Other Insurance Plan 1[/TD]
[TD]Other Insurance Number 1[/TD]
[TD]Other Insurance Group Number 1[/TD]
[TD]Other Insurance Phone 1[/TD]
[TD]Other Insurance Rx Bin 1[/TD]
[TD]Other Insurance Pcn 1[/TD]
[TD]Other Insurance Plan 2[/TD]
[TD]Other Insurance Number 2[/TD]
[TD]Other Insurance Group Number 2[/TD]
[TD]Other Insurance Phone 2[/TD]
[TD]Other Insurance Rx Bin 2[/TD]
[TD]Other Insurance Pcn 2[/TD]
[TD]Notes For Clinician[/TD]
[TD]Clinician Id[/TD]
[TD]Clinician Name[/TD]
[TD]Practice Name[/TD]
[TD]Supplier Id[/TD]
[TD]Supplier Name[/TD]
[TD]Intake Id[/TD]
[TD]Intake Name[/TD]
[TD]Marketer Id[/TD]
[TD]Marketer Name[/TD]
[TD]Agent Name[/TD]
[TD]Telemed[/TD]
[TD]Verification[/TD]
[TD]Shipping[/TD]
[TD]DME[/TD]
[TD]Back[/TD]
[TD]Right Shoulder[/TD]
[TD]Left Shoulder[/TD]
[TD]Right Knee[/TD]
[TD]Left Knee[/TD]
[TD]Right Ankle[/TD]
[TD]Left Ankle[/TD]
[TD]Right Wrist[/TD]
[TD]Left Wrist[/TD]
[/TR]
</tbody>[/TABLE]

I would like to extract the information based upon the supplier and have the entire row copied for each patient with said supplier. I would like to have sheets for each supplier. I need this to automatically populate each time the master sheet is updated. I tried using the advanced filter, which works perfectly, however we would have to do that manually in order to keep the sheets updated. Any advice would be greatly appreciated.

 

[Roderick_E]

a thing many VBA coders do is macro record a manual process they want to automate. So macro record yourself doing the advanced filter. Stop the recording. Look at the code. You can now use
Worksheet_Change from the master sheet to automatically trigger the advanced filter macro every time someone enters data into the Master Sheet. TEST TEST TEST to make sure it is working as expected.

HELP:
You may need to enable Developer option to see the Macro Record feature. Do so by clicking FILE/OPTIONS/Custom Ribbons, check Developer.

Worksheet_change, right click the Master sheet tab and VIEW CODE. Change General to Worksheet and Declarations to Change. CALL your recorded macro.

 

[Warrior1975]

Ok, thank you. I am going to attempt this. I've never done anything like this, so I am sure I will have more questions, but I believe I understand what you are suggesting. Thank you again!!

 

[mumps]

The Worksheet_Change macro will need a trigger in order to run automatically. You can add a column at the end of your data and call it something like "Updated". After updating any row, you can enter a letter such as "X" in that column to act as the trigger and automatically run the macro. If you have any questions, please feel free ask.

 

[Warrior1975]

Ok, I was able to record it. I viewed the code:



Sheets("1. Mindy- Inhouse Privates").Select
Range("A9:BP27").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:BP2"), CopyToRange:=Range("A9:BP9"), Unique:=False
Range("A9:BP27").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:BP2"), CopyToRange:=Range("A9:BP9"), Unique:=False
Range("AW2").Select
Selection.ClearContents
Range("A9:BP27").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:BP2"), CopyToRange:=Range("A9:BP9"), Unique:=False
Sheets("Master List").Range("A9:BP15").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:BP2"), CopyToRange:=Range("A9:BP9"), Unique:= _
False
End Sub

So, now you want me to enter this into the Master sheet tab? Do I have to change any of the coding? As we enter additional information, obviously there will be additional rows. I want to run it for everything in that sheet, are there any other changes I need to make?

 

[Warrior1975]

The Worksheet_Change macro will need a trigger in order to run automatically. You can add a column at the end of your data and call it something like "Updated". After updating any row, you can enter a letter such as "X" in that column to act as the trigger and automatically run the macro. If you have any questions, please feel free ask.

Ok, I will do that. That's all I would need to add? Thank you!! On a side note, I don't see a thank you, was trying to click on it to give thanks.

 

[Warrior1975]

Worksheet_change, right click the Master sheet tab and VIEW CODE. Change General to Worksheet and Declarations to Change. CALL your recorded macro.

Ok, I clicked on view code. I don't see an option to change General to Worksheet and Declarations to Change. It opens the Virtual Basic and I can paste the code there, but that's all I see. It does list my workbook and all the sheets. Thank you guys, I can't believe how quick you all are.

 

[mumps]

To thank someone, you can click on the word "Like" in the bottom right hand corner of the helper's post. You said that you want a sheet for each supplier. Are you creating the supplier sheets manually or did you want the macro to create them for you? What you want to do may take a little more than just recording a macro. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Warrior1975]

Ok, I will do that. I must have overlooked the like button.

 

[Warrior1975]

Here is a link with my file. It's basically blank but I entered 2 fictitious patients.

https://www.dropbox.com/sh/3e2tqxqg8rrsuks/AABjo5TPubUQiOVuXoP0iygAa?dl=0

Thank you in advance.