VBA/Excel change value of a cell in multiple worksheets from a range in another workb

So i have a workbook with around 500 worksheets, and i need to change the value of a cell, say A1, in each worksheet, to a value in a range from another workbook.
For example,
the value of A1 in Sheet1 in Workbook1 = A1 in Sheet1 of Workbook2
the value of A1 in Sheet2 in Workbook1 = A2 in Sheet1 of Workbook2
the value of A1 in Sheet3 in Workbook1 = A3 in Sheet1 of Workbook2
etc.
I've been trying to alter and use the following, but getting nowhere, any help would be appreciated. Thanks
Sub mycode()
For Each Worksheet InThisWorkbook.Sheets
Range(“A1″) = “Exceltip”
Next
End Sub

SOLVED

Sub TransferValues()
Dim workbook2 As Workbook
Dim i AsLong

Set workbook2 = Workbooks.Open("C://My Documents//SomeWorkbook2.xlsx")

For i =1To ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(i).Range("A1").Value = workbook2.Worksheets("Sheet1").Range("A1").Offset(i -1,0).Value
Next i

workbook2.Close SaveChanges:=False
EndSub