Macro that copies a row

I found a macro that almost does what I need. It copies a row named "Totals" and pastes the row in a new sheet. The row "Totals" have formulas and I need values. How do I adjust the macr开发者_开发百科o to work correctly?

Here is the macro code:

Sub RowCopy2()
Dim rngFind As Range

With Worksheets("Hol. Stats").UsedRange
Set rngFind = .Find("Totals", _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=True)
End With
rngFind.EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Use

Sub RowCopy2() Dim rngFind As Range
  With Worksheets("Hol. Stats").UsedRange 
    Set rngFind = .Find("Totals", _
         LookIn:=xlValues, _
         LookAt:=xlWhole, _
         SearchOrder:=xlByRows, _
         SearchDirection:=xlNext, _
         MatchCase:=True) 
  End With 

  rngFind.EntireRow.Copy 
  Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
End Sub

It is worthwhile making use of the range object to test if the search string is actually found, you could then take alternative action, provide info to the user etc

And fwiw you can bypass copy and paste by setting the values of the destination to the values of the source.

Sub RowCopy2()
Dim rngFind As Range
Set rngFind = Worksheets("Hol. Stats").UsedRange.Find("Totals", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFind Is Nothing Then Worksheets("Sheet2").Rows(1).Value = rngFind.EntireRow.Value
End Sub

It's quicker to find the row "Totals" using the rows range name. You can then do it in two lines...

Rows(Range("Totals").Row).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

That's the reason for using range names in Visual Basic. Other properties of ranges are covered in the text for this Excel help video