Copy/paste value from one workbook to another VBA

[mel13028]

I'm trying to change the date in several different workbooks. Right now my code just references the cell that contains the new date, but I want it to hard code that date into the workbooks instead. How can I change this? Here's my current code.

Dim Num As Double
Num = 8
Dim FilePath As String
Dim Number As Double
Number = 8
Dim WSCount
Do Until Cells(Num, 1).Value = ""


If Cells(Num, 1).Value = "Yes" Then



FilePath = ActiveWorkbook.Sheets("Sheet1").Range("B" & Number).Value
Workbooks.Open (ThisWorkbook.Path & "" & FilePath)

Range("ValDate") = "=DATE_macro.xlsm!ValDate"

Calculate

ActiveWorkbook.Save
ActiveWorkbook.Close
Number = Number + 1
Num = Num + 1
ElseIf Cells(Num, 1).Value = "No" Then

Num = Num + 1
Number = Number + 1


End If
Loop




End Sub

 

[MrKowz]

Try replacing the Range("ValDate") = "=DATE_macro.xlsm!ValDate" and Calculate lines with:

With Range("ValDate")
    .Formula = "=DATE_macro.xlsm!ValDate"
    Calculate
    .Copy
    .PasteSpecial xlPasteValues
End With