VBA: How to correctly copy and paste a range including formulas and formats without breaking references?
Could someone explain I'm working on a project and hit a roadblock. Hey everyone, I'm running into an issue that's driving me crazy. I'm working on a VBA script that needs to copy a specific range of cells from one worksheet to another, while preserving both the formulas and the formatting of the original cells. However, when I attempt to copy the range, it seems to break the references in the formulas, resulting in `#REF!` errors in the destination sheet. Here's the code I've been using: ```vba Sub CopyRangeWithFormulasAndFormats() Dim sourceSheet As Worksheet Dim destSheet As Worksheet Set sourceSheet = ThisWorkbook.Sheets("Sheet1") Set destSheet = ThisWorkbook.Sheets("Sheet2") sourceSheet.Range("A1:D10").Copy destSheet.Range("A1").PasteSpecial Paste:=xlPasteAll Application.CutCopyMode = False End Sub ``` I've tried using `xlPasteAll` in the `PasteSpecial` method, but it still breaks the references of the formulas in the copied range. I also checked the references in my formulas, and they are relative. Additionally, I've tried using `xlPasteFormulas` and `xlPasteFormats` separately, but then I lose the desired formatting or the actual formulas. When I manually copy and paste the same range in Excel, it works perfectly, so I suspect there’s something specific about how the VBA environment handles this. Could someone guide to figure out how to copy the range in a way that retains the original references in the formulas? Any insights or alternative methods would be greatly appreciated. I'm using Excel 2016. This is part of a larger web app I'm building. Am I missing something obvious? This is part of a larger service I'm building. Has anyone else encountered this?