CODEWITHSUNDEEP
vbaexcel
Oz Pere
Oz Pere

Reputation: 121

Excel - Error when trying to loop through workbooks

Good day friends, I'm receiving an error whenever I try to loop through all open workbooks in order to copy and paste to a master workbook. For the life of me I can't figure out how to rectify it, could any of you kindly assist?

Sub LoopCopyPaste()
Dim wb As Workbook
Dim Lastrow As Long
    For Each wb In Application.Workbooks
            If wb.Name <> "MasterDatabase.xlsx" & "MacrosExcelFile.xls" Then
                Lastrow = wb.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
                wb.Worksheets(1).Range("B7:J" & Lastrow).Copy
                ''
                Windows("MasterDatabase.xlsx").Activate
                Range("B" & Rows.Count).End(xlUp).Offset(1).Select
                ActiveSheet.Paste
            End If
    Next wb
End Sub

The error is "1004, Application-defined or object-defined error", and it points to the "Lastrow = wb.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row" sentence. What can I do to solve this issue? Thanks in advance.

Upvotes: 0

Views: 269

Answers (2)

user6432984
user6432984

Reputation:

SalvadorVayshun is correct

If wb.Name <> "MasterDatabase.xlsx" And wb.Name <> "MacrosExcelFile.xls" Then

Here is how I would refactor the code

Sub LoopCopyPaste()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Dim Lastrow As Long
    For Each wb In Application.Workbooks
        If wb.Name <> "MasterDatabase.xlsx" And wb.Name <> "MacrosExcelFile.xls" Then

            With wb.Worksheets(1)
                .Range("B7:J7", .Cells(.Rows.Count, 2).End(xlUp)).Copy
            End With

            With Workbooks("MasterDatabase.xlsx").Worksheets(1)
                .Range("B" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial
            End With

        End If
    Next wb
    Application.ScreenUpdating = True
End Sub

Values only

Sub LoopCopyPaste()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Dim Lastrow As Long
    Dim Data
    For Each wb In Application.Workbooks
        If wb.Name <> "MasterDatabase.xlsx" And wb.Name <> "MacrosExcelFile.xls" Then

            With wb.Worksheets(1)
                Data = .Range("B7:J7", .Cells(.Rows.Count, 2).End(xlUp)).Value
            End With

            With Workbooks("MasterDatabase.xlsx").Worksheets(1)
                .Range("B" & .Rows.Count).End(xlUp).Offset(1).Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
            End With

        End If
    Next wb
    Application.ScreenUpdating = True
End Sub

Values and Formulas

Sub LoopCopyPaste()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Dim Lastrow As Long
    Dim Data
    For Each wb In Application.Workbooks
        If wb.Name <> "MasterDatabase.xlsx" And wb.Name <> "MacrosExcelFile.xls" Then

            With wb.Worksheets(1)
                Data = .Range("B7:J7", .Cells(.Rows.Count, 2).End(xlUp)).Formula
            End With

            With Workbooks("MasterDatabase.xlsx").Worksheets(1)
                .Range("B" & .Rows.Count).End(xlUp).Offset(1).Resize(UBound(Data, 1), UBound(Data, 2)).Formula = Data
            End With

        End If
    Next wb
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

SalvadorVayshun
SalvadorVayshun

Reputation: 353

If wb.Name <> "MasterDatabase.xlsx" And wb.Name <> "MacrosExcelFile.xls" Then

Try changing that. I will update this answer when I test it a bit more.

Upvotes: 1

Related Questions