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:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
That error would suggest that one (or both) of those codenames is incorrect (or in a different workbook to the code).
 
Upvote 0
Are they in the same workbook as the code?
 
Upvote 0
I can't download files at work I'm afraid.
 
Upvote 0
There isn't a shData sheet in your workbook, nor do you have a shInvoiceTemplate
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top