CODEWITHSUNDEEP
vbaexcel
Anirudh Chauhan
Anirudh Chauhan

Reputation: 111

Using array to store the names of the workbooks

I have written a code which stores the names of the excel workbooks in an array and I am using it into a for loop.

Private Sub CommandButton1_Click()
Dim myArr  As Variant
Dim lngCounter  As Long

myArr = Array("Rahul", "Ameya")

For lngCounter = LBound(myArr) To UBound(myArr)

Set x = Workbooks.Open("C:\Users\*****\Desktop\New folder\" & 
myArr(lngCounter) & ".xls")

Set y = Workbooks.Open("C:\Users\ND18521\Desktop\New folder\" & 
myArr(lngCounter) & ".xls")

With x.Sheets(myArr(lngCounter))
Set aCell1 = .Range("A1:X1000").Find(What:="User", LookIn:=xlValues, 
LookAt:=xlWhole, _
                MatchCase:=False, SearchFormat:=False)
 .Range(aCell1, .Cells(.Rows.Count, aCell1.Column).End(xlUp)).Offset(2, 
  0).Copy _
 ThisWorkbook.Sheets(myArr(lngCounter)).Range("A2")
  End With

Next lngCounter
End Sub

But I am getting error on the below line :

With x.Sheets(myArr(lngCounter))

Error : Object required

Upvotes: 0

Views: 870

Answers (3)

Vityata
Vityata

Reputation: 43585

Whenever you use code from StackOverflow, try to understand what is staying behind it. Or at least use the same indentation as in the code. Having said this, try the following

  1. Create two files - Sheet1 and Sheet2 on your desktop.
  2. Run the following code.
  3. Try to adapt it.

Code:

Option Explicit

Public Sub TestMe()

    Dim myArr           As Variant
    Dim myWb            As Workbook
    Dim lngCounter      As Long

    myArr = Array("Sheet1", "Sheet2")

    For lngCounter = LBound(myArr) To UBound(myArr)

        Set myWb = Workbooks.Open("C:\Users\" & Environ$("Username") & "\Desktop\" _
                                              & myArr(lngCounter) & ".xlsx")

        With myWb.Worksheets(myArr(lngCounter))
            Debug.Print .Name
        End With

        myWb.Close False

    Next lngCounter

End Sub

Upvotes: 2

FunThomas
FunThomas

Reputation: 29296

I see some issues with the code, but my main concern is that you are looping over an array that holds filenames, but in the same moment you use these names as sheet names. Is this really what you want to do? Does the workbook Rahul has a worksheet Rahul and the workbook Ameya has a sheet Ameya?

Other things to mention:

  • You never use the workbook you assign to y

  • You don't declare variables x and y. Always use option explicit and declare all variables to avoid difficult to find bugs.

Upvotes: 0

Bla2
Bla2

Reputation: 61

Try this x.Worksheets(myArr(lngCounter))

Upvotes: 0

Related Questions