Excel Visual Basic Application-defined or Object-defined error

Question

So I have two excel worksheets in one workbook. There is a bigger one called "data" and smaller one called "Sheet1." The code below first creates a new sheet called "DailyReport" and compare the two sheets. If one cell from the Column B of the "Sheet1" is the same as one cell in the column B of "data", it should copy that row in "data" to the new sheet "DailyReport." Below is the code. It is a Macro. Now it keeps saying "Invalid procedure call or argument"error.

Sub DailyReportGenerator()
Sheets.Add.Name = "DailyReport"

Application.ScreenUpdating = False

Dim StartNumber As Integer
Dim EndNumber As Integer
Dim StartNumber2 As Integer

EndNumber = 9999

For StartNumber = 1 To EndNumber
    For StartNumber2 = 1 To EndNumber

    If Worksheets("Sheet1").Cells(StartNumber, "B").Value = Worksheets("data").Cells(StartNumber2, "B").Value Then
        Sheets("data").Range(Sheets("data").Cells(StartNumber, "B"), Sheets("data").Cells(StartNumber, "CA")).Copy Sheets("DailyReport").Cells("StartNumber, B")
    End If

    Next StartNumber2
Next StartNumber

Application.ScreenUpdating = True
End Sub

Sheets("DailyReportChristina").Range(Sheets("DailyReport")... this is your problem. — Tim Williams
also .Cells(StartNumber, B) should be .Cells(StartNumber, "B") — Dmitry Pavliv
@simoco Sorry for the mistake. That is on me. I adjust it. Now it keeps reporting "invalid procedure call or argument" on this line "Sheets("data").Range(Sheets("data").Cells(StartNumber, "B"), Sheets("data").Cells(StartNumber, "CA")).Copy Sheets("DailyReport").Cells("StartNumber, B")" — Angelo
change .Cells("StartNumber, B") to .Cells(StartNumber, "B") (at the end) — Jzz

Tim Williams Tim Williams · Accepted Answer · 2014-06-02T17:00:30

EDITED:

Sub DailyReportGenerator()

Const EndNumber As Long = 999

Dim shtRpt As Worksheet, shtData As Worksheet, shtSheet1 As Worksheet
Dim rwNumSht1 As Long, rwNumData As Long

Set shtRpt = Sheets.Add()
shtRpt.Name = "DailyReport"

Set shtData = Worksheets("data")
Set shtSheet1 = Worksheets("Sheet1")

Application.ScreenUpdating = False

For rwNumSht1 = 1 To EndNumber
    For rwNumData = 1 To EndNumber

    If shtSheet1.Cells(rwNumSht1, "B").Value = shtData.Cells(rwNumData, "B").Value Then
        With shtData
        .Range(.Cells(rwNumData, "B"), .Cells(rwNumData, "CA")).Copy _
                                           shtRpt.Cells(rwNumSht1, "B")
        End With
    End If

    Next rwNumData
Next rwNumSht1

Application.ScreenUpdating = True
End Sub

Excel Visual Basic Application-defined or Object-defined error

1 Answers