I can't get copy from one worksheet to another to work

[MarcMoolenaar]

I want to copy five ranges of cells from the active worksheet to sheet of another workbook. Somehow the selected data is not copied and the target ranges remain empty. This is what I did so far:

Sub KopieerSchutbladInfo()
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim sourceRange As Range
    Dim targetWorkbook As Workbook
    Dim targetWorksheet As Worksheet
    Dim targetRange As Range
    Dim sourceWorkbookPath As String
    Dim targetWorkbookPath As String
    Dim Targetline As Integer

    ' Define the path to the source workbook (that's the actual, already opened workbook)
    Set sourceWorkbook = ThisWorkbook
    Set sourceWorksheet = sourceWorkbook.ActiveSheet

    ' Define the path to the target workbook (this one is closed at this moment in time)
    targetWorkbookPath = "F:\Sales\Schutblad data.xlsx"
    Set targetWorkbook = Workbooks.Open(targetWorkbookPath)
    Set targetWorksheet = targetWorkbook.Sheets("Gegevens")
    Targetline = targetWorksheet.Range("A2").Value

    'Kopieer de gegevens van het schutblad naar het databestand

        ' Copy order data
        Set sourceRange = sourceWorksheet.Range("B4")
        Set targetRange = targetWorksheet.Range("A" & Targetline)
        sourceRange.Copy
        targetRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    ' Save and close the target workbook
    targetWorkbook.Save
    targetWorkbook.Close

End Sub

it seems simple enough ...

 

[MARK858]

If you add the Msgbox what does Targetline return and is it what you expected?

        Set targetRange = targetWorksheet.Range("A" & Targetline)
        MsgBox Targetline

I also take it that the code resides in sourceWorkbook and not your Personal workbook or any other workbook?

 

[MarcMoolenaar]

The TargetLine numbe shown is correct. The code is stored in my Personal workbook, I guess that might cause the problem. Any clue on how to solve this?

 

[MarcMoolenaar]

I change the "Set sourceWorkbook = ThisWorkbook" to Set sourceWorkbook = ActiveWorkbook" and it's working like a charm! Thanks for the winning hint!

 

[MARK858]

Happy it helped and welcome to the forum (and yes if you have code in the personal workbook then the workbook the code is working on should be the ActiveWorkbook [or a specified workbook], ThisWorkbook refers to the workbook the the code resides in)