Thanks Jacque for posting the file including the further info. The specification is still unclear to me.
If Department has a certain word in it (like master) I would like the entire column to copy to the Master worksheet.
(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
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.
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.