
Originally Posted by
syt0x
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.
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.
Bookmarks