Changing value of a cell in multiple worksheets from a range in another workbook

Question

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

Answer 1

Try this:

1. Open your destination workbook and store a reference to the workbook in a variable
2. Loop through the worksheets in your current workbook using a For loop
3. Fully qualify your references, using this new variable name and ThisWorkbook to distingish between ranges on different workbooks.

---

Sub TransferValues()
    Dim workbook2 As Workbook
    Dim i As Long

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

    For i = 1 To 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
End Sub

Answer 2

here's a variation on CallumDA code, mainly to optimize memory accesses and, hence, performance (should it ever be an issue):

Sub TransferValues()
    Dim myValues As Variant
    With Workbooks.Open("C://My Documents//SomeWorkbook2.xlsx") 'open and reference "source " workbook
        myValues = Application.Transpose(.Worksheets("Sheet1").Range("A1").Resize(ThisWorkbook.Worksheets.Count).Value) 'store referenced workbook "Sheet1" worksheet values in column A from row 1 down to "workbook1" (i.e. the one where the macro resides in) sheets number
        .Close False 'close referenced workbook
    End With

    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
        sht.Range("A1").Value = myValues(sht.Index)
    Next
End Sub