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
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