Paste special

[MrGJEB]

Is there a way in which you can use a macro to copy a row, (some cells values are numbers, some text and some are formulas) but only past the formulas.

as a simple example if i copy row 1

A1 = david
B1 = 5
C1 = 6
D1 = sum(B1,C1)

and copy it to row 2 i want the outcome to be

A2 = ""
B2 = ""
C2 = ""
D2 = sum(B2,C2)

Thanks for you help

 

[sanrv1f]

why not simply copy and paste and delete the non-formula cells?

copy and paste to a new range,

Press F5->Specials->Constants->Delete

 

[MrGJEB]

here is an extract fo the code that i have got the line in red is what flags the error. I am sure it is something simple but i just can not figure it out.


Dim NextCell As Range
Dim Previous As Range



Set Previous = Workbooks("New Corporate Communications Sheet.xls").Worksheets("Jobs").Range("A65536").End(xlUp).EntireRow

Set NextCell = Workbooks("New Corporate Communications Sheet.xls").Worksheets("Jobs").Range("A65536").End(xlUp).Offset(1, 0)

Previous.Copy

Previous.Offset(1, 0).PasteSpecial (xlPasteFormulas)

ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).Select

Application.CutCopyMode = False
Selection.ClearContents


With NextCell

.Value = TXTnewjobnumber.Text
.Offset(0, 1).Value = TXTnewjobtitle.Text
.Offset(0, 2).Value = TXTnewjobdescription.Text
.Offset(0, 3).Value = CBXprintpdf.Value
.Offset(0, 4).Value = CBXnewdepartment.Value
.Offset(0, 5).Value = CBXnewregion.Value
.Offset(0, 6).Value = TXTnewemail.Text
.Offset(0, 7).Value = CDate(Format(Now, "DD/MM/YYYY"))
.Offset(0, 9).Value = CBXnewassignedto.Value

 

[MrGJEB]

Just to give a bit more detail the error says that no cells were found, but the row is clearly selected!