Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT and format problems (2) | Excel Discussion (Misc queries) |