Hi all,

I am trying to copy columns from one workbook to another. Whenever I enter only one value (1st cell of the column) I get the error from the subject (error 1004). As soon as I add at least 2 values, then it works.

The code looks as follows:

Dim c4cWorkbook As Workbook

    Set c4cWorkbook = Workbooks.Open(ThisWorkbook.Path & "\CopyTest.xml")
    Dim sourceColumn As Range, targetColumn As Range
    
    ' ############ First Name ###########
    ' Select First Name Column
    Set sourceColumn = ThisWorkbook.Worksheets(1).Range("b4", ThisWorkbook.Sheets(1).Range("b4").End(xlDown))
    Set targetColumn = c4cWorkbook.Worksheets("Personal Data").Range("d8", c4cWorkbook.Worksheets("Personal Data").Range("d8").End(xlDown))
    
    ' Clear First Name Column in C4C template
    Cells(1, 1).Select
    targetColumn.Clear
    targetColumn.ClearFormats
    
    ' Copy column First Name to C4C template
    sourceColumn.Copy
    targetColumn.PasteSpecial xlPasteValues
Quite frustrating but I expect this can be solved easily.

Thanks in advance.

Cheers
Julian