Search code examples
excelvbaexcel-2010

How do i modify this code To copy and add the values and not the formulas?

I have this snipped of code, but i ran into a problem with a table that has some formulas in it, and it copies said formulas instead of the values

Book.Sheets(Site).Range("BE19:CN57").Copy
        OtherBook.Sheets(site).Range("BE19:CN57").PasteSpecial _
        Operation:=xlPasteSpecialOperationAdd

I have tried this alternate way of doing it

auxiliaryRangeMonth = Book.Sheets(site).Range("BE19:CN57")
    auxiliaryRangeYear = OtherBook.Sheets(site).Range("BE19:CN57")
    
    auxiliaryRangeYear.Value = auxiliaryRangeYear.Value + auxiliaryRangeMonth.Value

Neither of these approaches work properly

I expected one of these ways of doing it to work, but I am drawing a blank

Solution

  • If you want to only paste the values in a range, you can use the Paste parameter for the PasteSpecial method. Use xlPasteValues to copy only the values, or xlPasteValuesAndNumberFormats to copy the number formats as well (if important).

    Book.Sheets(Site).Range("BE19:CN57").Copy
OtherBook.Sheets(site).Range("BE19:CN57").PasteSpecial _
    Paste:=PasteValues, _
    Operation:=xlPasteSpecialOperationAdd