How do I skip certain cells VBA xlPasteValues command and keep the formulas in specific range-CodePudding

I'm trying to modify this VBA code so that it does not paste values for all the cells.

Sheets("Report").Copy
    Sheets(1).name = "PrintOut"
    Sheets("PrintOut").UsedRange.Copy
    Sheets("PrintOut").Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Now it copies and pastes a value for all cells and deletes the formulas. I need it to paste values for all other cells but I206 and I207. Formulas in these cells are needed in the new "PrintOut" sheet.

By changing the xlPasteValues to xlPasteFormulas it does paste the formulas, but to all cells. I don't know how to specify the ranges.

I also named the range in which I want to keep the formulas as "PrintOut_Formulas" in name manager, so this could be the range that needs to have the formulas in the "PrintOut".

Does somebody know a good method for this?

Thanks!

CodePudding user response：

Convert Range To Values 'Skipping an Area'

Sub ExportWorksheet()
    
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = swb.Sheets("Report")
    
    sws.Copy
    
    ' or (as before)
    'Sheets("Report").Copy ' not recommended
    
    Dim dwb As Workbook: Set dwb = Workbooks(Workbooks.Count)
    Dim dws As Worksheet: Set dws = dwb.Sheets(1)
    dws.Name = "PrintOut"
    
    ' Put the formulas in an array.
    Dim dfrg As Range: Set dfrg = dws.Range("I206:I207")
    Dim dfData As Variant: dfData = dfrg.Formula
    
    ' Convert to values (more efficient).
    With dws.UsedRange
        .Value = .Value
    End With
     
    ' Write back the formulas.
    dfrg.Formula = dfData
         
    'dwb.SaveAs "C:\Test\Test.xlsx"
    'dwb.Close SaveChanges:=False
    
End Sub