Problem copying cells to another workbook with VBA

[mcomp72]

I am trying to copy a range from one workbook to another via VBA code (the sheet name is the same in both workbooks), and it isn’t working correctly. I want to copy both the values (or formulas, if they exist), as well as the cell formatting.

Here’s the sub that does the copying:

Sub CopyRange(SheetName As String, rng As String)

Import_sourceWB.Sheets(SheetName).Range(rng).Copy
ThisWorkbook.Sheets(SheetName).Range(rng).PasteSpecial Paste:=xlPasteFormats
ThisWorkbook.Sheets(SheetName).Range(rng).PasteSpecial Paste:=xlPasteValues

End Sub

I received "Runtime error 1004: PasteSpecial method of Range class failed" on the line:

ThisWorkbook.Sheets(SheetName).Range(rng).PasteSpecial Paste:=xlPasteFormats

Things to note:
- Import_sourceWB is a global variable and I've confirmed it is set correctly.
- It's copying from the same sheet name on both workbooks, and I've confirmed the variable SheetName being passed to this sub is correct.
- The variable rng, in this case, is equal to "A9:F245". No cells is that range are locked.
- I've confirmed that the sheet I'm trying to copy to in ThisWorkbook is not protected.
- I tried flip-flopping the order of the two PasteSpecial lines of code, but either way, it fails on the first one listed.

I tried changing the sub to the following:

Sub CopyRange(SheetName As String, rng As String)

Import_sourceWB.Sheets(SheetName).Range(rng).Copy ThisWorkbook.Sheets(SheetName).Range(rng)

End Sub

In this case, the copy works, but it doesn't copy the cell formatting; only the values.

Anyone have any ideas how I could copy both the values/formulas and the cell formatting?

 

[mcomp72]

I took out some other code from my workbook that had something to do with copying & pasting, to see if that might be the cause of the problem. When I tried it after that, I got a different error. Again it was 1004, but the description said "This action won't work on multiple selections." The rng variable was equal to the same as in my above post: "A9:F245".

Sub CopyRange(SheetName As String, rng As String)

Import_sourceWB.Sheets(SheetName).Range(rng).Copy
ThisWorkbook.Sheets(SheetName).Range(rng).PasteSpecial Paste:=xlPasteFormats
ThisWorkbook.Sheets(SheetName).Range(rng).PasteSpecial Paste:=xlPasteValues

End Sub

Just like before, the error happened on the first PasteSpecial line.

 

[mcomp72]

I just tried to manually copy & paste from one workbook to another (into the workbook with the VBA code), and I tried just a single cell. I again got the "This action won't work on multiple selections." message. Anyone have any idea what could be causing that?

 

[Alex Blakenburg]

The only way I can that message it so select a non-contiguous range and select copy.
I assume you have tried a reboot ?

 

[mcomp72]

I actually discovered that the error is only happening when I try to copy from one specific workbook. If I try to copy from a newly created workbook, I don't get that error. So I guess the workbook I was trying to copy from must have been corrupted? Very strange... I had never seen that before in all my years working in Excel. I didn't know a workbook could become corrupted and not allow you to copy from it.

 

[Alex Blakenburg]

Hopefully you can recover the workbook.

I was going to suggest that after the next manual copy when you got the error message to run the below to see what it came up with in the debug window.

Sub nonContiguousSelectionCheck()

    Dim rng As Range
    Dim rCell As Range
    
    Set rng = Selection.Cells
    Debug.Print "No of Cells", Selection.Count
    
    For Each rCell In rng
        Debug.Print rCell.Address(, , , 1)
    Next rCell

End Sub