1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro to insert rows into Excel 2011 spreadsheet

Discussion in 'Business Applications' started by VCP1, Sep 5, 2012.

Thread Status:
Not open for further replies.
  1. VCP1

    VCP1 Thread Starter

    Joined:
    Sep 5, 2012
    Messages:
    2
    Hi
    I have set up the following macro to enable users of the spreadsheet to insert a row and copy the formating and formula's. For some reason, when I go to insert a new row again, it inserts the row in a completely random place and not in the exact same place. People may want to add 20 rows one after the other to populate and I am trying to enable them to do this without having to worry about the formulas in there (I want to protect the formula's to ensure they don't overwrite them). Can anyone help? This is my code from Excel 2007 (I recorded a macro, I am not competent and writing them from scratch):

    Sub InsertRowStock()
    '
    ' InsertRowStock Macro
    ' Insert a row into the stock sheet and copies all formats and formulas
    '
    ' Keyboard Shortcut: Ctrl+i
    '
    ActiveCell.Offset(27, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub
     
  2. VCP1

    VCP1 Thread Starter

    Joined:
    Sep 5, 2012
    Messages:
    2
    Just one other thing, I think the rows it is inserting are every 27 rows down but I want a row always to be inserted in the one specific place on the worksheet.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1067777

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice