PDA

View Full Version : Solved: Inserting new rows



danesrood
04-01-2012, 08:18 AM
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
04-01-2012, 10:04 AM
With ActiveCell

If .Row > 1 Then

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

danesrood
04-01-2012, 11:07 AM
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
04-01-2012, 03:45 PM
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
04-01-2012, 04:12 PM
typos...... (sigh)

danesrood
04-02-2012, 01:02 AM
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
04-02-2012, 01:45 AM
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
04-02-2012, 05:42 AM
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
04-02-2012, 07:38 AM
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
04-03-2012, 05:09 AM
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
04-03-2012, 05:24 AM
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
04-03-2012, 07:24 AM
xld

That's it perfect.

Sorry to have dragged it on a bit.

All the best

Bob Phillips
04-03-2012, 10:00 AM
No problem, the 'easy' problems often aren't so simple :)

danesrood
04-03-2012, 10:53 AM
Again my grateful thanks