Excel VBA: How to avoid the creation of links back to the “.xlsm” file on the “.xlsx” file, when copying a range from “.xlsm file to a “.xlsx” file?

by

When copying a range of cells from an Excel “.xlsm” to another Excel “.xlsx” file, I get a link to the “.xlsm” file that I can see in the “workbook links” in the “.xlsx” file.

Do anyone know the reason and how to avoid this behavior?

I’ve tried the following:

With ThisWorkbook
    lRow = .Sheets("name1").Cells(Rows.Count, 1).End(xlUp).Row
    With .Sheets("name1")
        .Range("A2:F" & lRow).Copy
        wb.Sheets("name1").Range("A2").PasteSpecial Link:=False, DisplayAsIcon:=False, _
                 NoHTMLFormatting:=True
    End With
    
    With .Sheets("name2")
        .Range(.Cells(1, 5), .Cells(37, lCol)).Copy Destination:=wb.Sheets("name2").Range("E1")
    End With
End With

And got: Runtime error '1004': Application-defined or object-defined error

  • This does not happen because of xlsm and xlsx files. Easy to test – create a brand new, clean book1.xlsm and book2.xlsx, and copy some ranges – do you get a link? Or, copy your problem ranges to another xlsm file – do you still get a link? The cause is probably what is in the ranges you are copying – tables? names?

    – 

  • Thanks, Kevin. I thought that it does not have to do with the extension of the file. However as it was happening while using this configuration, I decided to inform the exact environment used. I do not use tables nor named ranges in the source file. Also I do not copy the graphs or figures from the source file. Do you think that there is come possible benefit by using the PasteSpecial as a possible solution?

    – 

  • One additional info: the copy/paste between workbooks pastes formulas and values but not formats.

    – 




  • I tried the following: With ThisWorkbook lRow = .Sheets("name1").Cells(Rows.Count, 1).End(xlUp).Row With .Sheets("name1") .Range("A2:F" & lRow).Copy wb.Sheets("name1").Range("A2").PasteSpecial Link:=False, DisplayAsIcon:=False, _ NoHTMLFormatting:=True End With With .Sheets("name2") .Range(.Cells(1, 5), .Cells(37, lCol)).Copy Destination:=wb.Sheets("name2").Range("E1") End With End With And got: Runtime error ‘1004’: Application-defined or object-defined error Any suggestion, please.

    – 




  • The error above happened at the line: `wb.Sheets(“Baseline”).Range(“A2”).PasteSpecial Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

    – 

Leave a Comment