0

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.

Cenderze
  • 1,202
  • 5
  • 33
  • 56
  • 2
    I am pretty sure the first sheet in Excel is Sheet1 and not Sheet0, so you might need to change your code as, `Set openWs = openWb.Sheets(i + 1)` – PaulFrancis Mar 12 '15 at 11:24
  • Likewise, you have already put your sheet names into an array, so you could just call the sheet name from the array as Set openWs = openWb.Sheets(myHeadings(i)) – Dave Mar 12 '15 at 11:33
  • The sheet names are Januari, Februari etc. So the iteration from 0 would be the first element in the Array, which would be Januari as I understood. @Dave Thanks! This solved the error, but caused Another one to appear. I've edited the OP. – Cenderze Mar 12 '15 at 11:51
  • I've added that as an answer so you can accept it and this question will be shown as resolved. New problems such as what you have added in the edit should be created as new questions so they can be viewed separately. – Dave Mar 12 '15 at 14:46

1 Answers1

1

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))
Dave
  • 1,643
  • 1
  • 9
  • 9