CODEWITHSUNDEEP
excelvba
Krksa
Krksa

Reputation: 3

Subscript Out of Range after Second loop through

I am working on a project that will go find an csv file, open it, pull a specific column, and transpose it into a specific spot in the open workbook, and then move to the next file name in the list. Currently, the code works through the first two loops but then gets stuck at "Workbooks(fname).Activate" in the code below. I have figured out that the error is occuring due to the ActiveCell.Offset once it runs through the second loop, but I can not figure out why it would work for the first two and then just stop. Any help would be appreciated!

Sub x1853_Get_ALL_Dim3()

    Dim fname As String
    Dim Name As String
    Dim Path As String
    Dim Path2 As String
    
    Sheets("Sheet1").Select
    
    Range("A6").Select
    
    Do Until ActiveCell.Value = ""
    
    fname = Path2 & ActiveCell.Value
    
      Path = ThisWorkbook.Path
     Path2 = Path & "\Pull\"
    ChDrive Path2
    ChDir Path2
    
   Name = ThisWorkbook.Name
    
 Application.ScreenUpdating = False
    
    Workbooks.Open Filename:=fname, Local:=True
    ActiveWindow.SmallScroll Down:=0
    Range("B3:B300").Select
    ActiveWindow.SmallScroll Down:=-15
    Selection.Copy
    
    Workbooks(Name).Activate
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Workbooks(fname).Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Close SaveChanges:=False
    ActiveCell.Offset(0, -2).Range("A1").Select
    
    ActiveCell.Offset(1, 0).Select
    
    Loop
      
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 27

Answers (1)

BigBen
BigBen

Reputation: 50042

Workbooks.Open returns a workbook reference that you should capture. Change:

Workbooks.Open Filename:=fname, Local:=True

to

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=fname, Local:=True)

and then refer to wb instead of Workbooks(fname).

Perhaps more importantly, you should see How to avoid using Select and revise your code accordingly.

Upvotes: 1

Related Questions