Insert rows across multiple sheets copy formulas help

S

syt0x

New member
Joined
Jul 25, 2013
Messages
2
Reaction score
0
Points
0
Hello everyone. I’m 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. I’d finally like it to switch the view back to the Completed 2013 tab if possible. As a final note I’d 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.
 
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.
 
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.
 
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.
 
syt0x said:
Hello everyone. I’m 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. I’d finally like it to switch the view back to the Completed 2013 tab if possible. As a final note I’d 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.
Click to expand...

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 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?

View attachment KAH Test.xlsm
 
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
 
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.
 
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
 
millz said:
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.
Click to expand...

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
 
Hi mercifulkumquat,
Here is what I assumed you wanted.
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).Insert Shift:=xlDown
    rows(r).Copy
    rows(r + 1).PasteSpecial xlPasteFormulas
    
    For Each sName In Array("Input project data", "Required capacity in hours", "Capacity Utilization")
        If ActiveSheet.Name <> sName Then
            Sheets(sName).rows(r + 1).Insert Shift:=xlDown
            Sheets(sName).rows(r).Copy
            Sheets(sName).rows(r + 1).PasteSpecial xlPasteFormulas
        End If
    Next
    
    Application.CutCopyMode = False
End Sub
 
Millz, thanks for replying so fast!
This one gives me an error "For Each control variable must be Variant or Object".
What I found to work is this though:
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
    Sheets("Capacity Utilization").Rows(r + 1 & ":" & r + 1).Insert Shift:=xlDown
    Sheets("Capacity Utilization").Rows(r & ":" & r).Copy
    Sheets("Capacity Utilization").Rows(r + 1 & ":" & r + 1).PasteSpecial xlPasteFormulas
    
    Application.CutCopyMode = False
End Sub

Would it be okay to leave it like that, do you think?
Thanks again for your help!
 
Hi, sorry for that. I didn't test previously.
Change
Code: 
    Dim sName As String
to this (remove "As String")
Code: 
    Dim sName

The code that you have is not ideal, it was more catered for the OP. Since OP wanted to insert rows between two sets of sheets, A1<->A2, or B1<->B2. But for your case I think it's inserting rows for sheets 1<->2<->3, right?
 
You must log in or register to reply here.
Back
Top