仅复制列和粘贴公式-不复制值 [英] Copy column and paste formulas only - not values

问题描述

我试图将一列复制到表格的右侧，并仅粘贴公式(不粘贴值).

I'm trying to copy a column to the right of table and paste the formulas only (not values).

Sub acrescentaCols()
Dim oSheet As Worksheet

Set oSheet = Sheets("Sheet1")
oSheet.Columns("D:D").Select
    Selection.Copy
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

但这也将复制值(因为Excel也将值视为公式).

But this is copying also the values (because Excel considers values to be a formula too).

我该如何解决?

推荐答案

根据我之前的评论:

Sub acrescentaCols()
Dim oSheet As Worksheet
Dim rng As Range
Dim cel As Range
Set oSheet = Sheets("Sheet1")
With oSheet
    Set rng = .Range(.Range("D1"), .Range("D" & .Rows.Count).End(xlUp))
    For Each cel In rng
        If Left(cel.Formula, 1) = "=" Then
            Range(cel.Offset(, 1), cel.Offset(, 1).End(xlToRight)).Formular1c1 = cel.Formular1c1
        End If
    Next cel
End With
End Sub

这篇关于仅复制列和粘贴公式-不复制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！