[b]Insert new row??[/b]

[PaulDReed]

Help!!
I am writing a sheet where different users will enter data in rows,
which include validation features and also default data in some of the
cells.
I want to create a macro, operated by a button, where a user can
insert a 'new' row (which contains validation and default data), at
position row 2 (so the new row is directly below the headings.
I have tried (and failed miserably) but then, I am a newbie!
I have tried creating a dummy hidden row at row 2, but as the list is
filtered, I just get error messages.

Thanks

 

[LTunnicliffe]

Sub AddRowAt2()

  Range("A2").EntireRow.Insert
  Range("A3").EntireRow.Copy
  Range("A2").EntireRow.PasteSpecial xlPasteFormats
  Application.CutCopyMode = False
  Range("A2").Select

End Sub

This should do it for you!

 

[Barry Katcher]

LT, that won't give him the default data/formulas he needs. I like the idea of a hidden dummy row just below the headers. Just have to include in the macro to unhide, copy and paste and then hide again. Any reason this wouldn't work?

 

[Barry Katcher]

For instance, assuming row 1 is your headers, row 2 is your dummy row and is hidden, try this:

Sub Macro1()
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Rows("1:3").Select
    Selection.EntireRow.Hidden = False
    Rows("2:2").Select
    Selection.Copy
    Rows("3:3").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    Selection.EntireRow.Hidden = True
End Sub

 

[PaulDReed]

Barry

You're correct, the code that you wrote differs from mine, because yours unhides/hides the dummy row, whereas mine did not, which makes a significant difference.

By adding a few other bits, the macro works great.

Thanks

 

[Barry Katcher]

Your welcome, Paul. Wish they were all this easy. :D