Solved: Inserting new rows

[danesrood]

Dear All

My apologies for asking another insert rows question but other posts I have seen do not really seem to answer my question.

All that I would like is that from wherever the cursor is positioned for a copy of the row above to be inserted at the cursor position with all data deleted leaving just all of the formatting and data validations only.

I hope that this makes sense and as ever your help is most appreciated

[Bob Phillips]

With ActiveCell

If .Row > 1 Then

.Rows(.Row - 1).Copy
.Offset(0, -.Column + 1).psatespecial Paste:=xlpastformats
End If
End With

[danesrood]

xld

Thank you for your response

Not sure what I am missing but I'm getting the following error message:
Pastespecial method of Range class failed.

[Bob Phillips]

Did you correct my error


With ActiveCell

If .Row > 1 Then

.Rows(.Row - 1).Copy
.Offset(0, -.Column + 1).Pastespecial Paste:=xlPasteformats
End If
End With

[Aussiebear]

typos...... (sigh)

[danesrood]

xld

Sorry nothing is happening at all.

Looking at the code, is it supposed to be just copying the formats.

What I want is to copy the data validations as well.

Not that I'm sure how to do it but could you copy the whole row then delete the contents?

[Bob Phillips]

How is this?



Dim cell As Range

With ActiveCell

If .Row > 1 Then

For Each cell In .Offset(-1, 0).EntireRow.SpecialCells(xlCellTypeFormulas)

cell.Copy cell.Offset(1, 0)
Next cell

For Each cell In .Offset(-1, 0).EntireRow.SpecialCells(xlCellTypeAllValidation)

cell.Copy cell.Offset(1, 0)
cell.Offset(1, 0).Value = ""
Next cell
End If
End With

[danesrood]

xld

This is working fine for formulas and data validation cells but not for conditonal and basic formatting i.e. cell alignment, borders etc.

[Bob Phillips]

Dim cell As Range

With ActiveCell

If .Row > 1 Then

.Offset(-1, 0).EntireRow.Copy .Offset(0, -.Column + 1)
.Offset(0, -.Column + 1).EntireRow.ClearContents

For Each cell In .Offset(-1, 0).EntireRow.SpecialCells(xlCellTypeFormulas)

cell.Copy
cell.Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
Next cell
End If
End With

[danesrood]

xld

That's it works a treat.

Could I just ask one minor extra question. How can I move the cursor 1 cell to the right after it has copied all of the formulas?

I have one particular spreadsheet where I try to hide the two formulas in A & B so I would like the cursor to end up in column C.

But whatever, thank you so much for saving me a lot of time.

Regards

danesrood

[Bob Phillips]

Dim cell As Range

With ActiveCell

If .Row > 1 Then

.Offset(-1, 0).EntireRow.Copy .Offset(0, -.Column + 1)
.Offset(0, -.Column + 1).EntireRow.ClearContents

For Each cell In .Offset(-1, 0).EntireRow.SpecialCells(xlCellTypeFormulas)

cell.Copy
cell.Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
Next cell
End If

Cells(.Row, "C").Select
End With

[danesrood]

xld

That's it perfect.

Sorry to have dragged it on a bit.

All the best

[Bob Phillips]

No problem, the 'easy' problems often aren't so simple :)

[danesrood]

Again my grateful thanks