Consulting

Results 1 to 2 of 2

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

  1. #1

    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

  2. #2
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •