How can I copy data from a column to another worksheet based upon a word in one cell?

My worksheet has 3 rows; Room Number, Room Name, Department

If Department has a certain word in it (like master) I would like the entire column to copy to the Master worksheet.

I know how to get into Visual Basic for the individual sheet(s) but get confused when it comes to the code.

Jacque

Jacque,

What you want to do sounds very simple and there are many approaches. VBA is not required.

However, from your question, it is not clear how to best answer. And certainly not specifically.

Can you better describe the situation?

regards,
Fazza

I have attached the DB with a better phrased question at the top.

I have received a couple of replies via another forum but it still isn't working.

Jacque

Thanks Jacque for posting the file including the further info. The specification is still unclear to me.

Various thoughts.

(I now understand that column should be row.) What action/event triggers/initiates the update?

For example, the changing of a cell value in the "Department" field to "MASTER". If this trigger were used, "MASTER" might be entered in a cell when other data on the row is incomplete. So row data inserted in the other worksheet will be incomplete.

And what about when a row contains "MASTER" and it is changed to something else. Does the corresponding row need to be deleted from the other worksheet? This has not been specified yet.

Or, when other cells on the row are edited/updated - are those changes to somehow be reflected in the summary sheet? This has not been specified yet.

A button could be put on the data ("Basement") worksheet to manually force an update. Still, the exact specification is unclear so I can't advise further on this.

Better still, I think, is to have some code run when the "Basement" sheet is deactivated. The code would replace ALL the relevant data on the "Security-Master" worksheet. That is, deleted all the relevant old data and replace it by all the current data. I write relevant data because there is othere data on the "Security-Master" worksheet also. One would need to understand the other data to be able to advise specifically. Again, the specification is unclear.

Some ideas on the updating process, to run on "Basement" sheet deactivation.

1. Delete the old data on "Security-Master", run an autofilter on "Basement" to show just the records with "MASTER", copy the values and paste them into "Security-Master". Remove the autofilter. Maybe also set CutCopyMode to False

Or,

2. Maybe as simple as two lines? Update a defined name for the data - such as Range("A1").CurrentRegion.Name = "Whatever" Update via a query table data on the "Security-Master" worksheet. In its simplest form this could be as simple as something like SheetXX.QueryTables(1).Refresh if the query is preexisting and the path is constant. Defined name "Whatever" being an input into the query table.

If you better specify what you want, your solution path might be clearer.

If you use VBA in the solution and get stuck on any steps, post what you've done and I'm sure someone can help.

HTH

Another thought, Jacque.

You might consider using MS Access instead of Excel, particularly if you have lots of data, or want multiple users to access the file, or more easily control different levels of access for different users.

And impose rigorous database philosophy - which can really help sometimes.

cheers,
Fazza