Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On the attached spreadsheet, I'd appreciate any help in automating a row insertion with copying of the data in the rows above. For example, rows 10 and 11 have some initial merged cells, then some cells to separate out revenue and expenditures. By pressing a keyboard shortcut (in a macro) or any other way, I'd like to automate an insertion of a row right below (12 and 13) that copies the formatting (including the merged cells) and the data contained in rows 10 and 11.

    Basic concept is to release this to a wide variety of users so that they can input their own data after they insert a copied row below the 'master' row.

    Thanks.
    aapke
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can add this macro and set the options to a shortcut key

    Sub Insert1011()
    Rows("10:11").Copy
    Rows(Selection.Row).Insert Shift:=xlDown
    End Sub

    It will insert rows 10 and 11 in whatever row is selected

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Totally appreciate it! Thanks. But what would I have to modify this to in order for the macro to capture the row immediately above the selected cell? For instance, someone wants to copy and paste rows 200 and 211? I made that up...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Like this perhaps?

    Steve

    Code:
    Option Explicit
    Sub Insert2Above()
      Dim lRow As Long
      lRow = Selection.Row
      Rows(lRow - 2 & ":" & lRow - 1).Copy
      Rows(lRow).Insert Shift:=xlDown
      Application.CutCopyMode = False
    End Sub

  5. #5
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is so cool! Thank you! Exactly what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •