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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.