Extracting info from master sheet to multiple sheets in same workbook

Warrior1975

New Member
Joined
Nov 16, 2016
Messages
9
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:

Supplier Exam DateExam Posted DateExam Completed DateExam Deleted DateExam StatusExam Issue IdFirst NameMiddle NameLast NameSuffixDOBSexHeight InchesWeight LbsAddress 1Address 2CityStateZipHome PhoneWork PhoneCell PhoneEmailContact PersonContact Person PhonePrimary Care Physician NamePrimary Care Physician PhoneHas InsuranceMedicare NumberMedicaid PlanMedicaid NumberOther Insurance Plan 1Other Insurance Number 1Other Insurance Group Number 1Other Insurance Phone 1Other Insurance Rx Bin 1Other Insurance Pcn 1Other Insurance Plan 2Other Insurance Number 2Other Insurance Group Number 2Other Insurance Phone 2Other Insurance Rx Bin 2Other Insurance Pcn 2Notes For ClinicianClinician IdClinician NamePractice NameSupplier IdSupplier NameIntake IdIntake NameMarketer IdMarketer NameAgent NameTelemedVerificationShippingDMEBackRight ShoulderLeft ShoulderRight KneeLeft KneeRight AnkleLeft AnkleRight WristLeft Wrist

<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>
</tbody>

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.
 
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.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top