Results 1 to 10 of 10

Thread: Runtime error 424 Object Required
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Location
    London
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Runtime error 424 Object Required

    Hello,

    A "double click" on any of the cells in Column B "Trip" launches a routine which produces the following error at present.

    Date Trip Order No. Issue Detail
    31/08/2019 60A 1 Issue 1 blah blah blah
    31/08/2019 39B 2 Issue 2 blah
    21/08/2019 39B 3 Issue 2 text here
    31/07/2019 12a 4 Issue 2 text here
    31/03/2019 39B 5 Issue 2 was late
    01/08/2019 39x 6 Issue 2 blah blah blah
    31/08/2019 39B 7 Issue 2 text here
    31/08/2019 39B 8 Issue 2 text here


    Run-time error '424':
    Object required

    The routine generating the issue is as follows. The fail is on the first range reference, in blue. The names of the sheets in the workbook are "Data" and "Invoice Template"
    Code:
    Sub CreateInvoice(RowNum As Integer)
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Dim sh As Worksheet
    With shInvoiceTemplate
    .Range("C2") = shData.Range("A" & RowNum)
    .Range("D11") = shData.Range("B" & RowNum)
    .Range("D12") = shData.Range("C" & RowNum)
    .Range("B15") = shData.Range("D" & RowNum)
    .Range("D15") = shData.Range("F" & RowNum)
    .Range("D16") = shData.Range("G" & RowNum)
    .Range("D18") = shData.Range("E" & RowNum)
    End With
    FPath = "C:\Users\xxxx\Desktop\Invoice"
    Fname = Format(shInvoiceTemplate.Range("D10"), "mmmm yyyy") _
    & "_" & shInvoiceTemplate.Range("D12")
    shInvoiceTemplate.Copy
    ActiveSheet.Name = "InvTemp"
    Set wb = ActiveWorkbook
    Set sh = ActiveSheet
    sh.Name = Fname
    wb.SaveAs Filename:=FPath & "" & Fname
    wb.Close SaveChanges:=False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
    End Sub

    Can anyone point out where I am going wrong please?

    Much appreciated.
    Last edited by RoryA; Sep 12th, 2019 at 06:37 AM. Reason: Code tags

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 424 Object Required

    That error would suggest that one (or both) of those codenames is incorrect (or in a different workbook to the code).

  3. #3
    New Member
    Join Date
    Dec 2018
    Location
    London
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 424 Object Required

    Hello,

    Ive checked the codenames and they match.

    The sheets are present in the workbook

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 424 Object Required

    Are they in the same workbook as the code?

  5. #5
    New Member
    Join Date
    Dec 2018
    Location
    London
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 424 Object Required

    Heres a link to the WB if it will help

    https://1drv.ms/u/s!Agzs1HxPwDAdqSWQ...rUEdK?e=U2gJc8

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,815
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Runtime error 424 Object Required

    I can't download files at work I'm afraid.

  7. #7
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Runtime error 424 Object Required

    There isn't a shData sheet in your workbook, nor do you have a shInvoiceTemplate

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,192
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Runtime error 424 Object Required

    As Kyle123 has pointed point out those code names don't exist.
    Try
    Code:
    Sub CreateInvoice(RowNum As Integer)
       Application.ScreenUpdating = False
       Dim wb As Workbook
       Dim shData As Worksheet, sh As Worksheet
       Set shData = Sheets("Data")
       With Sheets("Invoice Template")
          .Range("C2") = shData.Range("A" & RowNum)
          .Range("D11") = shData.Range("B" & RowNum)
          .Range("D12") = shData.Range("C" & RowNum)
          .Range("B15") = shData.Range("D" & RowNum)
          .Range("D15") = shData.Range("F" & RowNum)
          .Range("D16") = shData.Range("G" & RowNum)
          .Range("D18") = shData.Range("E" & RowNum)
          FPath = "C:\Users\xxxx\Desktop\Invoice"
          Fname = Format(.Range("D10"), "mmmm yyyy") _
          & "_" & .Range("D12")
          .Copy
       End With
       ActiveSheet.Name = "InvTemp"
       Set wb = ActiveWorkbook
       Set sh = ActiveSheet
       sh.Name = Fname
       wb.SaveAs Filename:=FPath & "\" & Fname
       wb.Close SaveChanges:=False
       ThisWorkbook.Activate
       Application.ScreenUpdating = True
    End Sub
    although some of the ranges you are using don't match to the invoice sheet.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Dec 2018
    Location
    London
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Runtime error 424 Object Required

    Thank you, that works perfectly.

    This was based on a routine from the web. The issue came when I changed the sh* . Am I right in thinking that the crux of the issue was that the Code for the sheets was "sh*****" even though the tab said "*****" or something similar?

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,192
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Runtime error 424 Object Required

    Your code names Data & InvoiceTemplate they did not have the sh infront of them. so you could have used
    Code:
    Sub CreateInvoice(RowNum As Integer)
       Application.ScreenUpdating = False
       Dim wb As Workbook
       Dim sh As Worksheet
       
       With InvoiceTemplate
          .Range("C2") = Data.Range("A" & RowNum)
          .Range("D11") = Data.Range("B" & RowNum)
          .Range("D12") = Data.Range("C" & RowNum)
          .Range("B15") = Data.Range("D" & RowNum)
          .Range("D15") = Data.Range("F" & RowNum)
          .Range("D16") = Data.Range("G" & RowNum)
          .Range("D18") = Data.Range("E" & RowNum)
          FPath = "C:\Users\xxxx\Desktop\Invoice"
          Fname = Format(.Range("D10"), "mmmm yyyy") _
          & "_" & .Range("D12")
          .Copy
       End With
       ActiveSheet.Name = "InvTemp"
       Set wb = ActiveWorkbook
       Set sh = ActiveSheet
       sh.Name = Fname
       wb.SaveAs Filename:=FPath & "\" & Fname
       wb.Close SaveChanges:=False
       ThisWorkbook.Activate
       Application.ScreenUpdating = True
    End Sub
    But I have had problems using codenames that are the same as the sheet name.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •