Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Insert rows across multiple sheets copy formulas help

  1. #1

    Insert rows across multiple sheets copy formulas help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone. Im currently using MS Excel 2010 on Windows 7. My question is as follows:

    I am looking for help creating a VBA macro within excel. I currently have an excel document with 4 tab worksheets and they are named Completed 2013, Metrics 2013, Completed 2012, and Metrics 2012. What I want the macro to do is insert a row below the selected cell in the Completed 2013 tab then insert a row in the exact same spot in the Metrics 2013 tab and finally drag the formulas from the row above (still in the Metrics 2013 tab) and copy them into the newly inserted row. Id finally like it to switch the view back to the Completed 2013 tab if possible. As a final note Id also like to do this for the 2012 tabs but I assume I can just switch the sheet names within the code? Thanks for your help!

    This question has been posted on a few other help forums and if I receive an answer there before here I will edit this post to say so.

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi syt0x. Did you get an answer at another forum on this? Note that people often won't answer a question that has been posted elsewhere, because cross-posters often don't update their posts.

    If you haven't got an answer, post back and let me know and I'll take a look. In future I'd suggest you don't post on multiple forums, but instead just pick one and see if someone answers it. You'll almost certainly get a quicker outcome.

  3. #3
    I realize that but I've posted on one site before and sat there with no answer for a long while. I need this figured out quickly as my deadline is approaching and I've been struggling with it for a few weeks. I still have not received an answer on any of the sites I posted.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Cool. Probably would be good next time to include your above sentence so that helpers know why you have cross-posted.

    If you upload a sample workbook, I'll take a look.

  5. #5
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Quote Originally Posted by syt0x View Post
    Hello everyone. Im currently using MS Excel 2010 on Windows 7. My question is as follows:

    I am looking for help creating a VBA macro within excel. I currently have an excel document with 4 tab worksheets and they are named Completed 2013, Metrics 2013, Completed 2012, and Metrics 2012. What I want the macro to do is insert a row below the selected cell in the Completed 2013 tab then insert a row in the exact same spot in the Metrics 2013 tab and finally drag the formulas from the row above (still in the Metrics 2013 tab) and copy them into the newly inserted row. Id finally like it to switch the view back to the Completed 2013 tab if possible. As a final note Id also like to do this for the 2012 tabs but I assume I can just switch the sheet names within the code? Thanks for your help!

    This question has been posted on a few other help forums and if I receive an answer there before here I will edit this post to say so.
    Code:
    Sub InsertRow()
        If ActiveSheet.Name <> "Completed 2013" And ActiveSheet.Name <> "Completed 2012" Then Exit Sub
        Dim c, r As Long
        Dim sName As String
        c = Selection.Column
        r = Selection.row
        
        rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        rows(r & ":" & r).Copy
        rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        sName = IIf(ActiveSheet.Name = "Completed 2013", "Metrics 2013", "Metrics 2012")
        Sheets(sName).rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        Sheets(sName).rows(r & ":" & r).Copy
        Sheets(sName).rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        Application.CutCopyMode = False
    End Sub
    Basically this macro will only work when you have worksheet Completed 2013 or Completed 2012 selected. Tested with Excel 2007, should work with 2010 too.

  6. #6

    Question

    Hi Millz,

    I have a document here that would benefit from your Script. I want to insert a Row on Tab 1 and Tab 2 and then copy formula from the above cells (for both tabs), line to be inserted under the cell selected.

    Any ideas?

    KAH Test.xlsm

  7. #7
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Hi ejk3v, it sounds like the exact same thing as what the OP needs to do. You can just make some amendments to the macro to make it work for you. However, I see that you are using a button to run the macro, it this case you may need to select a specific range of cells and insert (instead of the entire row, since inserting a row where the button lies will sort of expand the button's height).

    Try something like this, not exactly the best way to select a range of cells but it should work:
    Code:
    Range("A" & CStr(r + 1) & ":F" & CStr(r + 1)).Insert Shift:xlDown

  8. #8
    Millz,

    The script previous to my message does not copy above rows formula/data? How can I ammend the script to do this on multiple tabs?

    If you are able to add the script to my uploaded document, that would be very helpful indeed.

  9. #9
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    Hi ejk3v, the single line code was part of the amendments required to be made to the code in my first post, in order to make it work for you. Here is what you can try:
    Code:
    Sub InsertRow()
        Dim r As Long
        r = Selection.row
        
        Range("A" & CStr(r + 1) & ":F" & CStr(r + 1)).Insert Shift:xlDown
        Range("A" & CStr(r) & ":F" & CStr(r)).Copy
        Range("A" & CStr(r + 1) & ":F" & CStr(r + 1)).PasteSpecial xlPasteFormulas
        
        Sheets("Tab 2").Range("A" & CStr(r + 1) & ":F" & CStr(r + 1)).Insert Shift:=xlDown
        Sheets("Tab 2").Range("A" & CStr(r) & ":F" & CStr(r)).Copy
        Sheets("Tab 2").Range("A" & CStr(r + 1) & ":F" & CStr(r + 1)).PasteSpecial xlPasteFormulas
        
        Application.CutCopyMode = False
    End Sub

  10. #10
    Quote Originally Posted by millz View Post
    Code:
    Sub InsertRow()
        If ActiveSheet.Name <> "Completed 2013" And ActiveSheet.Name <> "Completed 2012" Then Exit Sub
        Dim c, r As Long
        Dim sName As String
        c = Selection.Column
        r = Selection.row
        
        rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        rows(r & ":" & r).Copy
        rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        sName = IIf(ActiveSheet.Name = "Completed 2013", "Metrics 2013", "Metrics 2012")
        Sheets(sName).rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        Sheets(sName).rows(r & ":" & r).Copy
        Sheets(sName).rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        Application.CutCopyMode = False
    End Sub
    Basically this macro will only work when you have worksheet Completed 2013 or Completed 2012 selected. Tested with Excel 2007, should work with 2010 too.
    Hi millz,
    I hope you are still here on the forums and able to help.
    I've used your code to do the same thing that the OP needs - to insert a row on the sheet "Input project data" and have it do the same thing automatically on sheets "Required capacity in hours" and "Capacity Utilization". But for some reason the macro only works for the first two sheets (input project data and required capacity in hours) and doesn't to anything for "Capacity Utilization". Am I doing something wrong? here's the code I have:

    Code:
    Sub InsertRow()
        If ActiveSheet.Name <> "Input project data" And ActiveSheet.Name <> "Required capacity in hours" And ActiveSheet.Name <> "Capacity Utilization" Then Exit Sub
        Dim c, r As Long
        Dim sName As String
        c = Selection.Column
        r = Selection.Row
        
        Rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        Rows(r & ":" & r).Copy
        Rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        sName = IIf(ActiveSheet.Name = "Input project data", "Required capacity in hours", "Capacity Utilization")
        Sheets(sName).Rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
        Sheets(sName).Rows(r & ":" & r).Copy
        Sheets(sName).Rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
        
        Application.CutCopyMode = False
    End Sub

Page 1 of 2 1 2 LastLast

Posting Permissions

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