Subscript out of range when copying values to another sheet

[ipbr21054]

The code i have in use is shown below.
I am working with workbook DR & worksheet INV
I wish to copy some value & put them on another sheet.
The other workbook is MOTORCYCLES & the worksheet is INVOICES.

So far my code open the workbook in question, It then selects the correct worksheet & inserts a new row at Row 3
BUT now i see scubscript out of range.

Do you see an issue with my code & whilst there to save me starting a new post do you see any other issues.
Thansk

Private Sub SendToBikeSheet_Click()
Dim answer As Long, wb As Workbook
    answer = MsgBox("SEND VALUE'S TO BIKE WORKSHEET ?" & vbNewLine & "" & vbNewLine & "***** DO WE CONTINUE TO TRANSFER ? *****", vbYesNo + vbInformation, "BIKE TRANSFER QUESTION")
    If answer = vbYes Then
        Set wb = Workbooks.Open(fileName:="C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
        
        Workbooks("MOTORCYCLES.xlsm").Sheets("INVOICES").Activate
        
        ActiveSheet.Rows("3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
               
        Workbooks("DR.xlsm").Sheets("INV").Range("G13").Copy ' CUSTOMERS NAME
        wb.Sheets("MOTORCYCLES").Range("A3").PasteSpecial xlPasteValues
        
        Workbooks("DR.xlsm").Sheets("INV").Range("L16").Copy ' FRAME NUMBER
        wb.Sheets("MOTORCYCLES").Range("B3").PasteSpecial xlPasteValues
        
        Workbooks("DR.xlsm").Sheets("INV").Range("L15").Copy ' REGISTRATION
        wb.Sheets("MOTORCYCLES").Range("C3").PasteSpecial xlPasteValues
        
        Workbooks("DR.xlsm").Sheets("INV").Range("F32").Copy ' DATE OF JOB
        wb.Sheets("MOTORCYCLES").Range("F3").PasteSpecial xlPasteValues
            
        Workbooks("DR.xlsm").Sheets("INV").Range("F32").Copy ' INVOICE NUMBER
        wb.Sheets("MOTORCYCLES").Range("G3").PasteSpecial xlPasteValues
        
        wb.Close True

        
        Else
        Exit Sub
        
        End If
        Workbooks("DR.xlsm").Sheets("INV").Range("G13").Select
        Application.CutCopyMode = False
        MsgBox "BIKE TRANSFER COMPLETED", vbInformation, "SUCCESSFUL MESSAGE"
        ActiveWorkbook.Save

End Sub

 

[RoryA]

A couple of questions:

1. Which line causes the error?
2. Which workbook is this code in?

 

[ipbr21054]

The code in is workbook DR
I am sending to workbook MOTORCYCLES

The line in yellow starts when i try to transfer.
Here.

wb.Sheets("MOTORCYCLES").Range("A3").PasteSpecial xlPasteValues

 

[ipbr21054]

Here you can see sheet selected & new ro inserted.

 

[RoryA]

Isn't the sheet name INVOICES not MOTORCYCLES?

 

[ipbr21054]

Yep just spotted it

Can you advise correct way for this to only paste value and not background color etc.

wb.Sheets("INVOICES").Range("A3").PasteSpecial xlPasteValues

 

[RoryA]

That does only paste values.

 

[ipbr21054]

Still having an issue when putting values in cells.

 

[RoryA]

You wrote xlPastValues not xlPasteValues. Since you're going one cell at a time, I'd just assign the values directly. I'd also use a couple of worksheet variables to make the code easier to maintain - for example:

Private Sub SendToBikeSheet_Click()
   Dim sourceSheet As Worksheet
   Set sourceSheet = ThisWorkbook.Sheets("INV")
   
   Dim answer As Long
   answer = MsgBox("SEND VALUES TO BIKE WORKSHEET?" & vbNewLine & "" & vbNewLine & _
            "***** DO WE CONTINUE TO TRANSFER? *****", vbYesNo + vbInformation, "BIKE TRANSFER QUESTION")
   
   If answer = vbYes Then
      Dim wb As Workbook
      Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
      
      Dim destSheet As Worksheet
      Set destSheet = wb.Sheets("INVOICES")
      
      With destSheet
         .Rows("3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
         .Range("A3").Value = sourceSheet.Range("G13").Value ' CUSTOMERS NAME
         .Range("B3").Value = sourceSheet.Range("L16").Value ' FRAME NUMBER
         .Range("C3").Value = sourceSheet.Range("L15").Value ' REGISTRATION
         .Range("F3").Value = sourceSheet.Range("L13").Value ' DATE OF JOB
         .Range("G3").Value = sourceSheet.Range("L4").Value ' INVOICE NUMBER
      End With
      
      wb.Close True
      
      MsgBox "BIKE TRANSFER COMPLETED", vbInformation, "SUCCESSFUL MESSAGE"
      ThisWorkbook.Save
   End If

End Sub

 

[ipbr21054]

My mistake sorry & thanks for the help