Qfyilyi

At "New folder" I have the excel workbooks which will be opened by the loop; I want to copy 2 columns in each of these workbooks and paste it in another workbook called "new"

When I run the code I get the Run-time error '91': Object variable or With block variable not set

at line With wb.Worksheets(5) and only data of the first workbook are copied.

How can I fix it?

Option Explicit



Sub ProcessFiles()



Dim Filename, Pathname As String

Dim wb As Workbook

Dim wbMain As Workbook

Dim i As Integer



Set wbMain = Workbooks.Open("C:UsersADesktopVBAnew.xlsx")

Pathname = "C:UsersADesktopVBANew folder"



Filename = Dir(Pathname)

i = 1



Do While Filename <> ""

    Set wb = Workbooks.Open(Pathname & Filename)

    Enter_Formulas wb, wbMain, i



    wb.Close SaveChanges:=True

    Filename = Dir()

    i = i + 2

Loop   

End Sub





Sub Enter_Formulas(wb As Workbook, wbMain As Workbook, i)

With wb.Worksheets(5)

    .Columns(1).Copy Destination:=wbMain.Worksheets(2).Columns(i)

    .Columns(3).Copy Destination:=wbMain.Worksheets(2).Columns(i + 1)

End With   

End Sub

You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.