Below is a code that I now have working, It takes the sheet "INITIATING DEVICES" and searches for column "G" if column "G" has a value of "Yes" then it will copy that row, only columns "A, B, C" to worksheet "MESSAGE CHANGES". The only problem is that it must be run via a Macro command or a virtual button and is not very good when you need it right away. I was wondering how I could make the code run automatically so that the second the "Yes" is selected from the dropdown list the user would be moved to the "MESSAGE CHANGES" page so that they could input the information. It would need to seek the next available row and it can not overwrite the data above it. The idea is that the inspector can record all the message changes needed for the site and have a record of it. The values need to be copied only nothing more. I learned the hard way that paste and pastespecial do two different things lol.

================================================
Sub test()
Dim r As Range, filt As Range
Set ws = Sheets("INITIATING DEVICES") {I added this just to try and see if I could force the sheet because some of the forums are saying that could be the problem}
'Worksheets("INITIATING DEVICES").Activate
Set r = Range(Range("A6"), Range("G13324")) 'changae this if necessary =$A$7:$G$13224 {this is the range of the selected items columns A to G all the way down}

r.AutoFilter field:=Range("G7").Column, Criteria1:="Yes"
Set filt = r.SpecialCells(xlCellTypeVisible)
r.Columns("A:C").Copy
With Worksheets("MESSAGE CHANGES")

.Range("A7").PasteSpecial xlPasteValues
End With
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
End Sub
=====================================
"INITIATING DEVICES" is the page where the data will be found "MESSAGE CHANGES" is the page where the data will be copied if "Yes" is selected in column "G

Thanks for any help that can be given.