Problem copying cells to another workbook with VBA

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
202
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
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:

VBA Code:
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:

VBA Code:
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
202
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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".

VBA Code:
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

Board Regular
Joined
Aug 14, 2016
Messages
202
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 14, 2016
Messages
202
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,255
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Forum statistics

Threads
1,147,823
Messages
5,743,409
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top