Transfer Data to separate tables based two variables (site and product)

Hi

1) Will all the output sheets have all the Practices and Products? Or will you have to add items that don't exist.
2) I'm presuming that you want to put data into the data entry, then push a button and have that data transferred, then clear out the data entry tab.
3) What happens if there is already an entry for the particular practice / product combination?

rylo

1) Will all the output sheets have all the Practices and Products? (Yes)

2) I'm presuming that you want to put data into the data entry, then push a button and have that data transferred, then clear out the data entry tab (Yes, exactly)

3) What happens if there is already an entry for the particular practice / product combination (It should be overwritten. This is going to be updated as a current state document. So when something changes the previous values should just be overriden)

Did this help?

Hi

You will need to specify the array base to use so at the top of the general module enter

option base 1

Then try

Sub aaa()
  shtarr = Array("UnitCost_TBL", "UnitsTotal_TBL", "LaborCost_TBL", "LaborHRs_TBL", "Total_TBL")
  For Each ce In Range("A6:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    outrow = WorksheetFunction.Match(ce, Sheets("UnitCost_TBL").Range("A:A"), 0)
    outcol = WorksheetFunction.Match(Range("B1"), Sheets("UnitCost_TBL").Rows("1:1"), 0)
    For I = LBound(shtarr) To UBound(shtarr)
      Sheets(shtarr(I)).Cells(outrow, outcol).Value = ce.Offset(0, I).Value
    Next I
  Next ce
End Sub

This will paste the values to the relevant sheets.

See how that goes. Note that in the example file you are missing the product Call Bells In Pt Bathrooms (Nurse Call System). As this has been designed with all the products in the output sheets, it will just error.

rylo

This works great! Thanks for your help!!