Runtime error 424 Object Required

davellc

New Member
Joined
Dec 6, 2018
Messages
7
Hello,

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

DateTripOrder No.IssueDetail
31/08/201960A1Issue 1blah blah blah
31/08/201939B2Issue 2blah
21/08/201939B3Issue 2text here
31/07/201912a4Issue 2text here
31/03/201939B5Issue 2was late
01/08/201939x6Issue 2blah blah blah
31/08/201939B7Issue 2text here
31/08/201939B8Issue 2text here

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


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"
Rich (BB 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 a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,316
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
That error would suggest that one (or both) of those codenames is incorrect (or in a different workbook to the code).
 

davellc

New Member
Joined
Dec 6, 2018
Messages
7
Hello,

Ive checked the codenames and they match.

The sheets are present in the workbook
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,316
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Are they in the same workbook as the code?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,316
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I can't download files at work I'm afraid.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,684
There isn't a shData sheet in your workbook, nor do you have a shInvoiceTemplate
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,954
Office Version
365
Platform
Windows
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.
 

davellc

New Member
Joined
Dec 6, 2018
Messages
7
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,954
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,081,472
Messages
5,358,886
Members
400,514
Latest member
JoHio2577

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top