One way is to do the copy and paste normally.
Then go back to the pasted data and clean up the constants from there.
This may give you an idea:
Option Explicit
Sub testme02()
Dim rngToCopy As Range
Dim DestCell As Range
Set rngToCopy = Worksheets("sheet1").Range("a1:c9")
Set DestCell = Worksheets("sheet2").Range("a1")
rngToCopy.Copy _
Destination:=DestCell
With DestCell.Resize(rngToCopy.Rows.Count, rngToCopy.Columns.Count)
On Error Resume Next
.Cells.SpecialCells(xlCellTypeConstants).ClearCont ents
On Error GoTo 0
End With
End Sub
The "on error" stuff is there just in case there were no constants in that
range.
Kevryl wrote:
I want to set up a macro that copies a range to a new range, copying formulae
and cell formats, but ignoring raw data existing in the range copied from. In
other words I'm expanding a blank copy for a new period. One way would be to
name a blank copy in a range elsewhere and copy it in at the cursor, but for
various reasons I'd prefer not to do it that way this time.
I have made a 2 stage copy procedure, using the "Paste Special" Alt ESF and
Alt EST routines, but this process also copies raw data, presumably seeing
the data as a label, even although I have formatted the relevant cells as
numbers.
Has anyone encountered and solved this problem?
K
--
Dave Peterson
|