Copying range format and formulae without data
 

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

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