Copy To A Range But Only To Special Cell Types
Suppose you have a column with mixed cell types (some are formulas and others are constants). If you want to copy to this range, but want to avoid the formulas, the example below shows how to accomplish this using the SpecialCells property.
Program Code
Option Explicit
Dim Copycell As Range
Dim MyRange As Range
Dim constantCells As Range
' ******************************************************************
' This Will Copy Only To Target Cells That Have A Constant Value
' And Not Copy To Cells With Formulas
' ******************************************************************
' Assume That Cells in Rows 1 - 10 Have Constant Values
' And Cells in Rows 11 - 100 Have Formulas
' Only Cells in Rows 1 - 10 Will Be Updated by the Copy
' And Cells in Rows 11 - 100 Will Be Ignored Since They are Formulas
' ******************************************************************
' Assume That B10 Has The Value We Want To Copy
' ******************************************************************
Sub DontCopyToFormulas()
Set Copycell = Range("B10")
Set MyRange = Range("A1:A100")
Set constantCells = MyRange.SpecialCells(xlCellTypeConstants)
Copycell.Copy _
Destination:=constantCells
End Sub