CODEWITHSUNDEEP
excelvba
user4907546
user4907546

Reputation: 121

Paste values from formulas

Ok, I know how to do this normally, but I'm using some coding I'm not particularly familiar with, and I can't get it to work. This code uses a date to search through all the worksheets and paste the rows with that date on a Report tab. Unfortunately it is pasting the formulas rather than the values. 

For shtNum = 5 To Worksheets.Count

'Search Column a for date(s)

     With Sheets(shtNum).Columns(2)

      Set d = .Find(MyDate)

        If Not d Is Nothing Then

           firstAddress = d.Address

             Do

'Copy each Row where date is found to next empty Row on Summary sheet

                d.EntireRow.Copy Sheets("Reports").Range("A" & nxtRw)

                 nxtRw = nxtRw + 1

                Set d = .FindNext(d)

            Loop While Not d Is Nothing And d.Address <> firstAddress

        End If

     End With

 Next

This code works very well, but since I'm not sure how 

d.EntireRow.Copy Sheets("Reports").Range("A" & nxtRw)

Does what it does, I don't know how to edit the code and make it work.

Upvotes: 1

Views: 552

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Replace:

d.EntireRow.Copy Sheets("Reports").Range("A" & nxtRw)

with:

d.EntireRow.Copy
Sheets("Reports").Range("A" & nxtRw).PasteSpecial Paste:=xlPasteValues

Upvotes: 4

Related Questions