Insert new line with formatting - Excel

[gtg93]

Hi All,

 

I've created a mileage claim form in Excel, which has a list of support sites in there, and is linked to Google Maps to auto populate the distance travelled. All working great apart from one thing I can't get my head around.

 

Once the last row before the Total row is full, I want a new row inserting automatically. I've managed to achieve this with:

 

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = [TotalVal].Row - 1 Then
     Application.EnableEvents = False
     [TotalVal].EntireRow.Insert
     Application.EnableEvents = True
  End If
End Sub

 

But, I need all the formatting to remain the same as the rows above, as there are formulas in each cell etc...

 

Anyone got any ideas if this is possible? If so how it's done?

 

Ideally the document needs to be password protected from editing too, but not sure if this is possible if new lines are going to be inserted? It currently is protected, but this prevents the above code from running.

 

Any advice appreciated.

Edited May 11, 2015 by gtg93

[pcstru]

You could programatically select the row (range), copy it and then paste the formatting only.

 

(Hint : PasteSpecial Paste:=xlPasteFormats)

 

There are probably neater solutions.

Edited May 11, 2015 by pcstru

[Seb1780]

If you use the command Insert Shift:=xlDown this will insert any copied cells and move the rest of the worksheet down. This will include formulae and formatting.

[gtg93]

So far I have

 

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = [TotalVal].Row - 1 Then
     Application.EnableEvents = False
     [TotalVal].EntireRow.Insert
     Application.EnableEvents = True
  End If
  
  
  Set FormCopy = Sheets("Mileage Claims").Range("B13:L13")
  Set FormPaste = [TotalVal].Row - 1
   
   FormCopy.Copy
   FormPaste.PasteSpecial xlPasteFormats
   
   
   
End Sub

 

But currently getting Runtime Error 13, Type Mismatch on line:

Set FormPaste = [TotalVal].Row - 1

I assume it's the "[TotalVal].Row - 1" that can't be used in this instance - any ideas for a way around this?

Edited May 11, 2015 by gtg93

[Seb1780]

Try this method (as shown here)

 

'~~> Set your range

Set rng = .Rows(ActiveCell.Row)

 

'~~> Copy the range

rng.Copy

 

'~~> Insert the range

rng.Insert Shift:=xlDown

[gtg93]

It's likely me, but I can't seem to get that to do anything...

 

However, correct me if I'm wrong, but wouldn't that copy the formatting and the contents of the cells above? I'm just looking to copy the formatting of the cells down so there's a new line.

 

 

 

EDIT:

 

 

 

However, correct me if I'm wrong, but wouldn't that copy the formatting and the contents of the cells above

 

No - because it'd be inserting a new line, NOT copying anything - I missed that. Sorry! Let me have another go with that.

Edited May 11, 2015 by gtg93

[Seb1780]

However, correct me if I'm wrong, but wouldn't that copy the formatting and the contents of the cells above?

 

Yes, it will copy the formatting AND the contents, but from what you stated earlier I thought you wanted the contents copied across where they are formulas. I realise it will also copy entered data but this can then be deleted / overwritten.

[gtg93]

Try this method (as shown here)

 

 

So I've got that working - the only issue is it copies the contents of the cell too, so I've decided that if I can use this, but insert cells from another worksheet then perfect! I've tried adapting it, and it ran with no errors, just didn't insert any rows... any idea how I'd go about doing this with the code?

 

Appreciate all the help!

 

I thought you wanted the contents copied across

 

I do, but only some of them have the forumlas...

 

it looks like this:

 

There are two hidden columns, the Mileage columns has the GMaps formula, and the Origin and Destination contain drop down menus of different sites. Does that make sense?

 

Sorry for the confusion - thanks again!

Edited May 11, 2015 by gtg93

[Seb1780]

PM sent