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.
Originally Posted by syt0x
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.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
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
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
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
Posting Permissions
Reply With Quote
Bookmarks