PDA

View Full Version : [SOLVED] VBA/Excel change value of a cell in multiple worksheets from a range in another workb



jerram123
02-14-2018, 02:29 AM
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

jerram123
02-14-2018, 03:02 AM
SOLVED

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