Returning #REF! Error

[DarrenF]

Hello,

I'm running the below macro that I'm finding data from a table that I've created. The issue that I'm running into is the table that I have created does not have all the information I'm looking for side by side. At the end of the table I've created 5 column's to pull the information I'm looking for with (Example: =G2). When I run the below macro it doesn't using the information shown in the cell, but uses the formula that I'm using, which believe is why it's returning the #REF ! error. Is there a way that I can change up my macro below to pull the data in the cell and not the formula?

Sub find_data()


Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim selectdate As String
Dim selectname As String
Dim finalrow As Integer
Dim i As Integer


Set datasheet = Sheet4
Set reportsheet = Sheet3
selectdate = reportsheet.Range("B1").Value
selectname = reportsheet.Range("C1").Value


reportsheet.Range("B5:F30").ClearContents


datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To finalrow
If Cells(i, 15) = selectdate And Cells(i, 30) = selectname Then
Range(Cells(i, 31), Cells(i, 35)).Copy
reportsheet.Select
Range("B1500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
datasheet.Select
End If

Next i


reportsheet.Select
Range("B5").Select


End Sub

 

[Fluff]

Use

xlPasteValuesAndNumberFormats

rather than

xlPasteFormulasAndNumberFormats

 

[DarrenF]

That was easy, Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback