CODEWITHSUNDEEP
vbaexcel
Cenderze
Cenderze

Reputation: 1212

Copy the value from a cell in a worksheet into a range of cells

I'm intending to conduct a macro which will open up a workbook from the specified path, and loop through its worksheets which has the names "Januari, Februari, Mars" specifically, to deduct the value from C34. C34 has a value recorded there every time, so it shouldn't change. However I want to copy it to the current worksheet, where the first target should be at AA73, the second at AA74 etc. My code is

Sub Test()
     Dim myHeadings
     Dim i As Long
     Dim path As String
     path = "C:\pathtofile\file.xlsx"
     Dim currentWb As Workbook
     Set currentWb = ActiveWorkbook
     Dim openWb As Workbook
     Set openWb = Workbooks.Open(path)
     Dim openWs As Worksheet

     myHeadings = Array("Januari", "Februari", "Mars")

     For i = 0 To UBound(myHeadings)
       Set openWs = openWb.Sheets("&i")

       currentWb.Sheets("Indata").Range("AA73+Application.Match(i,Array,False)").Value = openWs.Range("C34").Value

    Next i
 End Sub

However the compiler says that the subscript is out of range at the row with

Set openWs = openWb.Sheets("&i")

Here I've tried to do "i", i, &i among other things, but it haven't changed. Also I've tried to use "ThisWorkbook" instead of "ActiveWorkbook" but it didn't help either. Does anybody have an input as to how to achieve this in a more proper way?

EDIT: Adapting to the response from Dave, it works to import the sheets. However I get an error in:

currentWb.Sheets("Indata").Range("AA73+Application.Match(i,Array,False)").Value = openWs.Range("C34").Value

Where I get Automation Error -2147221080 (800401a8) at said code snippet.

Upvotes: 0

Views: 130

Answers (1)

Dave
Dave

Reputation: 1643

You have already put your sheet names into an array, so you can just call the sheet name from the array as:

Set openWs = openWb.Sheets(myHeadings(i))

Upvotes: 1

Related Questions