Bear with me as I am a complete newbie to VBA (I only started learning it yesterday through google searching and trial and error). I am trying to link a cell from one workbook to another workbook using vba.
This is the code I have so far and it is the closest I have gotten to what I want the program to do. The cell that is being linked (B39) is an average of cells B5:B35, so this code pastes the AVERAGE(#REF!)
into my other file instead of the actual value. It also gives me an error "'1004': PasteSpecial method of Range class failed." I have also tried ActiveSheet.Paste Link:= True and got different errors with nothing pasted
on my second workbook. I think that this must be linked (and not just copying and pasting values) in order to accomplish what is needed (being able to enter values in one workbook and have them automatically transferred to another in the future as well as the present), but if you have a better way to do so I am open to suggestions. Thanks in advance!



Sub linkcell()
Dim sourcewbk As Workbook
Dim wbnew As Workbook
Dim dataIn As String
Dim dataOut As String

dataIn = "L:monthly_data.xlsx"
dataOut = "L:yearly_data.xlsx"

Set sourcewbk = Workbooks.Open(dataIn)
sourcewbk.Activate
With sourcewbk.Sheets("JAN11")
.Range("B39").Copy
End With


Set wbnew = Workbooks.Open(dataOut)
wbnew.Activate
With wbnew.Sheets("2011")
.Range("B5").PasteSpecial Paste:=xlPasteAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:= _
False, Transpose:=False
End With
End Sub