rngFoundAll.EntireRow.Copy Destination:=rngPaste

[micko1]

Need help with sintax. I have used the attached code to cut and paste entire row to new sheet. Problem is it does not paste special eg formulas, is it possible to add code so all formats and formulas copy across.
Thanks for looking.

Mick

Private Sub Move_Compliant_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String
Set rngPaste = Sheets("Regional Contractors").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("O")
Set rngFound = rngToSearch.Find(What:="Compliant", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete 'Optional to Delete
End If
End Sub

 

[VoG]

Maybe like this

rngFoundAll.EntireRow.Copy
rngPaste.PasteSpecial Paste:=xlPasteFormats
rngFoundAll.EntireRow.Copy Destination:=rngPaste

 

[Andrew Poulsom]

The line of code in the thread subject copies everything - formula and formats.

 

[micko1]

Firstly, Thanks heaps guys for your help. Not sure why but the macro moves the rows from one spreadsheet to the other but does not always move the formulas and conditional formating, Not sure what is going on. The purpose of the workbook is to move listed contractors from sheet to sheet depending on their compliance. If they are compliant they live on the front page, when and if they become non compliant their details are moved onto the second sheet called "NonCompliant", if they then become compliant they obviosly move back onto the main list. I guess you get the drift. Is it possible to send you a sample of the workbook for you to look at for me.
Thanks again for your help.

Mick