Runtime error 424 Object Required

[davellc]

Hello,

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

[TABLE="width: 633"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Trip[/TD]
[TD]Order No.[/TD]
[TD]Issue[/TD]
[TD]Detail[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD]60A[/TD]
[TD="align: right"]1[/TD]
[TD]Issue 1[/TD]
[TD]blah blah blah[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD]39B[/TD]
[TD="align: right"]2[/TD]
[TD]Issue 2[/TD]
[TD]blah[/TD]
[/TR]
[TR]
[TD="align: right"]21/08/2019[/TD]
[TD]39B[/TD]
[TD="align: right"]3[/TD]
[TD]Issue 2[/TD]
[TD]text here[/TD]
[/TR]
[TR]
[TD="align: right"]31/07/2019[/TD]
[TD]12a[/TD]
[TD="align: right"]4[/TD]
[TD]Issue 2[/TD]
[TD]text here[/TD]
[/TR]
[TR]
[TD="align: right"]31/03/2019[/TD]
[TD]39B[/TD]
[TD="align: right"]5[/TD]
[TD]Issue 2[/TD]
[TD]was late[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2019[/TD]
[TD]39x[/TD]
[TD="align: right"]6[/TD]
[TD]Issue 2[/TD]
[TD]blah blah blah[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD]39B[/TD]
[TD="align: right"]7[/TD]
[TD]Issue 2[/TD]
[TD]text here[/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2019[/TD]
[TD]39B[/TD]
[TD="align: right"]8[/TD]
[TD]Issue 2[/TD]
[TD]text here[/TD]
[/TR]
</tbody>[/TABLE]


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"

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.

 

[RoryA]

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

 

[davellc]

Hello,

Ive checked the codenames and they match.

The sheets are present in the workbook

 

[RoryA]

Are they in the same workbook as the code?

 

[davellc]

Heres a link to the WB if it will help

https://1drv.ms/u/s!Agzs1HxPwDAdqSWQu-w_M-urUEdK?e=U2gJc8

 

[RoryA]

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

 

[Kyle123]

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

 

[Fluff]

As Kyle123 has pointed point out those code names don't exist.
Try

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]

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]

Your code names Data & InvoiceTemplate they did not have the sh infront of them. so you could have used

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.