PasteSpecial method of range class fails in Function

Question

I've been searching around about my problem, but still can't find the solution. my goal is I want to copy a range from workbook A and paste it to workbook B. Below my code:

Function fillDistributor(ByVal kodis As String)

Dim wbThis As Workbook
Dim wbTarget As Workbook
Dim fileName As String

Set wbThis = Workbooks.Open(fileName:="D:\Work\Master Data\Testing JMC\" & kodis & "\" & kodis & " Manual Template.xlsx")
wbThis.Sheets("Distributor").Activate
Range("B13:S14").Select
Selection.Copy

Set wbTarget = Workbooks.Open(fileName:="D:\Work\Master Data\Testing JMC\" & kodis & "\" & kodis & "-Distributor.xlsx")
wbTarget.Sheets("Sheet1").Activate
Range("A1:R2").Select
Selection.PasteSpecial Paste:=xlPasteAll

End Function

The error is

Pastespecial method of range class failed.

Any help would be appreciated!

Answer 1

You are writing this as a function. It needs to be a sub. Functions are intended to return a value to a calling process. Subs are meant to perform actions on ranges or variables within a workbook/worksheet.

Sub fillDistributor(kodis As String)
    Dim wbThis As Workbook, wbTarget As Workbook
    'Dim fileName As String  '<- what is this used for?

    Set wbThis = Workbooks.Open(fileName:="D:\Work\Master Data\Testing JMC\" & kodis & "\" & kodis & " Manual Template.xlsx")
    Set wbTarget = Workbooks.Open(fileName:="D:\Work\Master Data\Testing JMC\" & kodis & "\" & kodis & "-Distributor.xlsx")

    wbThis.Sheets("Distributor").Range("B13:S14").Copy _
      Destination:=wbTarget.Sheets("Sheet1").Range("A1")

    wbThis.Close
    wbTarget.Close

End Sub

A sub can accept parameters passed in just as a function can.