Copy rows from one worksheet to another worksheet based on a cell criteria.

Hi,

I am trying to copy rows of data to separate worksheets based on a value in column A of a master worksheet.

My workbook has five worksheets - Calc (The value to match for extraction is in a drop down in column A), Surface, Middle, Bottom (These correspond to the values in the drop down in column A in the Calc sheet), and Data1 (this is a sheet that populates data into the Calc sheet based on a drop down list in column B of the Calc sheet).

What I need is a way to have all the rows that match Surface, Middle and Bottom in column A of the Calc sheet to populate onto the worksheets called Surface, Middle and Bottom. I have copied the header row from the Calc worksheet into the Surface, Middle and Bottom sheets so the copied rows will have to start populating into the first blank row under the header row. I need to keep all of the rows on the Calc worksheet.

I have been searching for a formula to use for this to no avail. I tried to construct a formula using lookups and matching but had no success. I'm not sure if there is a simple formula for this or not. I suspect that VBA may be the solution however I know nothing about VBA and though I have seen other posts with a similar situation to mine, I am unsure what values to substitute to make it work in my case.

Any help that you can provide is greatly appreciated.

Thanks