Match Date and Copy Paste - Object Required Error

**StephenR** · 09-05-2011, 10:40 AM

This

Range(A, i)

should either be

Range("A" & i)

or

Cells(i,"A")

Note that you don't need to select sheets. E.g.

Sheets("Daily Report").Range("C6").Value=Today

is equivalent to

Sheets("Daily Report").Select
Today = Range("C6").Value

**ashleys.nl** · 09-05-2011, 10:59 AM

THANK YOU SOOOOO MUCH! it was just a silly mistake but you've helped me with my project soo much! thank you ! Thank you.

Here's the working code in case others run into this problem, I still have to clean up the ".Select"s but other than that it works.

Sub LookCopy()


Dim Today As Double
Dim Test As Double
Dim i As Integer



Today = Worksheets("Daily Report").Cells(6, "C").Value

For i = 1 To 700

Sheets("Production Data - 09").Select

If Worksheets("Daily Report").Cells(6, "C").Value = Worksheets("Production Data - 09").Cells(i, "A").Value Then
Sheets("Daily Report").Select
Range("E30").Select
Selection.Copy
Sheets("Production Data - 09").Select
Range("B" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues

End If

Next i

End Sub

**snb** · 09-05-2011, 11:19 AM

Thank you for posting, but there's more to clean...

Sub LookCopy()
 on error resume next
 with sheets("Daily Report").Cells(6, 3)
  sheets("Production Data - 09").columns(1).find(.cells(6,3).value,,xlvalues, xlwhole).offset(,1)=.cells(30,5).value
 end with
End Sub

**ashleys.nl** · 09-05-2011, 11:33 AM

Thanks Again SNB, but when I run this code nothing seems to happen. I'm looking to make this as clean as possible because I'm eventually going to have to copy and paste about 30 values over 6 workbooks. in the same fashion, by finding the right date reference. Am I implementing the code correctly? I've created a new module in VB and enter the following code. But, upon running , nothing happens? Also, I took out the Resume on Error, because If there was an error I wanted to know. I'm still not confident enough in my coding.

Sub LookCopyCleanTest()

 With Sheets("Daily Report").Cells(6, 3)
  Sheets("Production Data - 09").Columns(1).Find(.Cells(6, 3).Value, , xlValues, xlWhole).Offset(, 1) = .Cells(30, 5).Value
 End With
End Sub

**snb** · 09-05-2011, 11:44 AM

Try:

 
Sub LookCopyCleanTest()

With Sheets("Daily Report").Cells(6, 3)
  Sheets("Production Data - 09").Columns(1).Find(.Cells(6, 3).Value, , xlFormulas, xlWhole).Offset(, 1) = .Cells(30, 5).Value
End With
End Sub

**ashleys.nl** · 09-05-2011, 11:49 AM

Now I'm getting a "Object Variable or With Block Variable Not Set"

Could it have something to do with the ", ," within the find function?

the Code below is what I'm trying to Run

Sub LookCopyCleanTest()

 With Sheets("Daily Report").Cells(6, 3)
  Sheets("Production Data - 09").Columns(1).Find(.Value, , xlValues, xlWhole).Offset(, 1) = .Cells(30, 5).Value
 End With
End Sub