Runtime error 424 Object Required

[CaptainCsaba]

Hey everyone!
I created a macro that would make our job easier. For some odd reason it was working fine and afer addig some lines and then deleting them it started giving me this Object error. I am no expert, I might have accidentally deleted something but I don't know what could be the problem. Does somebody know? The macro looks like this:

Sub Nortrust2()


'Cella unmergelés és rendezés
Sheet1.Activate
Sheet1.Cells.Select
Selection.UnMerge
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft


Sheet1.Activate
Sheet1.Cells.Select
With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With


'Registration oszlop létrehizása A oszlopban
Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(RC:RC[25]<>""""),RC:RC[25])"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A522")
    Range("A1:A5000").Select
    
Range("A1:A5000").Select
    Selection.Copy
    Range("A5001").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A5001:A10000").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'Fund Manager oszlop létrehozása L oszlopban
Range("L1:AC5000").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=LOOKUP(2,1/(RC[1]:RC[17]<>""""),RC[1]:RC[17])"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A5000")
    Range("A1:A5000").Select
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").Select
    Selection.Cut Destination:=Columns("M:M")
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    
'Betűtípus és méret rendezés
Range("A1:W5522").Select
    With Selection.Font
        .Name = "Calibri"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 5
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With


'Filter és szépítgetés
Cells.Select
Range("A2").Activate
Selection.RowHeight = 7.5
Cells.EntireColumn.AutoFit


Selection.AutoFilter Field:=12, Criteria1:="<>#N/A", _
        Operator:=xlAnd
        
Range("A5001:A10000").Select
    Selection.ClearContents


End Sub

 

[RoryA]

Which line gives the error?

Did you delete any worksheets?

 

[CaptainCsaba]

It does not specify a line. yeah I deleted one because it became chaotic but I renamed the new one to "Table 1" which was it's previous name. Is this the problem?

 

[RoryA]

Your code refers to Sheet1 which is the code name of the sheet. If you deleted that sheet, the new sheet probably won't have the same code name. You need to check in the VB Editor to see what the correct code name is.

 

[CaptainCsaba]

I renamed the table to "Sheet 1" "Sheet1" "Table 1" and "Table1". Gives me the same error with all tries. What could be the problem?

 

[RoryA]

Changing the caption on the sheet tab doesn't affect the code name. In the VB Editor, in the project explorer window you can view all the sheets in the workbook and you'll see the code name followed by the name on the tab in brackets. You need to change the code name in your code.

 

[CaptainCsaba]

Thank you it worked!

 

[RoryA]

Glad to help.

 

[jlyn123]

Hi,

I am getting the same error but no line is identified as to what object is missing. I am at a loss because I use this same code in another form and it works perfectly? I changed the naming of this forms workbook and everything. I can get the PDF to generate in the email but not the excel document as I am getting the object required error?

Any help would be greatly appreciated. Code below.
Sub Click()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Dim xlObj As Excel.Application
Dim wkbk As Object
Dim strUser As String
Dim strAttachment As String

strUser = Environ("Username")

Title = Worksheets("ACH Transfers").Range("A1") & " - " & DateTime.Now



PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False

DoCmd.OutputTo acOutputxl, "ExcelWorkbook(*.xlsx)", "C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx", False, "", , acExportQualityPrint

Set wkbk = xlObj.Workbooks.Open("C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx")

On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

On Error Resume Next
Set xlObj = GetObject(, "Excel.Application")
If Err Then
Set xlObj = CreateObject("Excel.Application")
IsCreated = True
End If
xlObj.Visible = True
On Error GoTo 0


strAttachment = "C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & "xlsx"

wkbk.Save
wkbk.Close
xlObj.Quit
Set xlObj = Nothing

With OutlApp.CreateItem(0)

.Subject = Title
.To = Worksheets("ACH Transfers").Range("J2")
.Body = "REQUESTOR tasks:" & vbLf _
& "1. Complete the Treasury Controllership Funding Request." & vbLf _
& "2. Email request to APPROVER to review." & vbLf _
& "3. NOTE: Do NOT email (To: or cc to IACTreasuryBanking@mmc.com. The APPROVER is the only person who should send an approval to this address." & vbLf & vbLf _
& "APPROVER tasks:" & vbLf _
& "1. Please review the attached file." & vbLf _
& "2. Forward your approval along with this form to IACTreasuryBanking@mmc.com" & vbLf & vbLf _
& "NOTE: Any request without proper approval will not be processed." & vbLf
.Attachments.Add PdfFile
.Attachments.Add strAttachment
.Display

End With

Kill PdfFile

Kill strAttachment

If IsCreated Then OutlApp.Quit

Set OutlApp = Nothing

End Sub