CODEWITHSUNDEEP
excelvba
M.J
M.J

Reputation: 5

Copy columns in a new workbook; Run-time error

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:\Users\A\Desktop\VBA\new.xlsx")
Pathname = "C:\Users\A\Desktop\VBA\New 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

Upvotes: 0

Views: 27

Answers (1)

Michal
Michal

Reputation: 6064

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.

Upvotes: 0

Related Questions