VBA: How to prevent automatic formatting changes in Excel when copying and pasting data?
I've been working on this all day and I’m working on a VBA script that copies data from one worksheet to another, but I'm working with issues with Excel automatically changing the formatting of the pasted data. For instance, when I copy a range of cells that contain numbers formatted as text, Excel automatically converts them to numbers on the destination sheet, which I don't want. I want to maintain the original formatting. I've tried using the `PasteSpecial` method with `xlPasteValues`, but it still seems to alter the formatting. Here’s a snippet of my code: ```vba Sub CopyData() Dim sourceSheet As Worksheet Dim destSheet As Worksheet Set sourceSheet = ThisWorkbook.Sheets("Source") Set destSheet = ThisWorkbook.Sheets("Destination") sourceSheet.Range("A1:B10").Copy destSheet.Range("A1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub ``` I also considered using `Value` assignments instead of `Copy` and `PasteSpecial`, but the original formatting is still lost when I paste it into the destination sheet. When I check the Format property of the destination range, it doesn't retain the text formatting from the source. Is there a way to copy the data without losing the original formatting, or to prevent Excel from applying automatic formatting? Any suggestions or workarounds would be greatly appreciated! I recently upgraded to Vba LTS.