mcomp72
Active Member
- Joined
- Aug 14, 2016
- Messages
- 275
- Office Version
- 365
- 2019
- 2016
- 2011
- Platform
- Windows
- 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:
I received "Runtime error 1004: PasteSpecial method of Range class failed" on the line:
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:
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?
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?