Copying range format and formulae without data

K

Kevryl

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
 
D

Dave Peterson

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).ClearContents
On Error GoTo 0
End With

End Sub

The "on error" stuff is there just in case there were no constants in that
range.
 
Top