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.

 

[mumps]

In your original post, you had the supplier name in column A. In your file there is no supplier name associated with each patient. Does your actual file have the supplier name in column A? We need the supplier name for each patient, otherwise we have no way of linking patient to supplier name. Perhaps you could post an updated file. Please post the Excel file not the zipped version. It makes things easier.

 

[Warrior1975]

Yes, that was my mistake. This is a work in progress, I forgot to add the supplier name to that file. I will add it now.

Not sure why it's compressing. I just uploaded the file to DB and created the link.

https://www.dropbox.com/s/zzam5gqx9xnslbr/DME Sample.xlsx?dl=0

 

[mumps]

Start by entering a header such as "Updated" in cell BR9 of the "Master List" sheet. Next, copy and paste the macro below into the worksheet code module. Do the following: right click the tab for your "Master List" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Place an "x" in any row with data in column BR and exit the cell. That row will be copied to the appropriate sheet. Please note that the supplier name in column A must be exactly the same as the supplier name in the sheet name. In your sample file, you have "Neil" in column A and "Neal" in the sheet name.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("BR:BR")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    If Target = "x" Then
        For Each ws In Sheets
            If ws.Name Like "*" & Cells(Target.Row, 1) & "*" Then
                Range("B" & Target.Row & ":BQ" & Target.Row).Copy ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub

 

[Warrior1975]

Ok, I tried and it's not working. Not sure what I did wrong. I am attaching a link for DB with what I've done. I am sure it's something i screwed up. https://www.dropbox.com/s/pkw3drya14cqvms/DME with Macro.xlsx?dl=0

Thank you again for your help.

 

[mumps]

Click this link to download your file and give it another try: https://app.box.com/s/qbgsfvy0frvk9rbcap1duur1ntgzwg9m