VBA code for adding a row in two sheets at one time

Question

I am not a programmer but have been filling that role as my company asks me to create worksheets for day to day processes so I apologize in advance if I do not state my question in the correct terms and if you have to break down your answer like you are talking to a total Noob as I am one.

Question: I want to set up a button that will allow a user to add a row to the active sheet they are working on. In the background I want the spreadsheet to add a row in another sheet in the same workbook in the same location as the one that was added. (the two sheets are identical only with the non active worksheet having info from the previous month) The reason I need it to do this is because the Active worksheet is pulling data from the inactive sheet and I want to keep the same number of rows in both sheets with the data on both in the same rows so it will pull the correct data.

Thank you for any advice you have!

Is the row being added the top row, a pre-determined row, or a selection based on the user? — Joe Laviano
This sounds like a headache. What if someone removes a row? What if they remove multiple rows at once? Are you going to lock down the page so that rows cannot be inserted or removed except via your code? The two pages shouldn't have to be synced up perfectly if the references already exist. Excel will update the address for the references upon a row insert. — Daniel
Joe-based on a selection of the user. The idea is they can add lines to a budget worksheet in order to further dissect the budget under one line item. — user2993438
Daniel - It is a headache. I need both sheets to line up because this file will get saved as a new file monthly....the idea being that the user can copy and paste data from current active sheet into the older sheet each month and then blow out the current months data and start the process over again. It the cells don't line up it seems to cause issues with how data is being pulled. — user2993438
This Works but with Errors that I can't Clear Sub AddRow() ' ' Row Macro ActiveWorkbook.Names.Add Name:="CopyRow", RefersToR1C1:=Rows(ActiveCell.Row) Range("CopyRow").Select Selection.Copy Dim Lst As Long Lst = ActiveCell.Row Worksheets("SOV Detailed Breakdown").Rows(Lst).Insert Worksheets("Previous Application").Rows(Lst).Insert Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False ActiveWorkbook.Names("CopyRow").Delete End Sub — user2993438

enhzflep enhzflep · Accepted Answer · 2013-11-14T19:11:41

From a quick play with the macro-recorder, I'm left with the following code:

Sub insertRow()
    Dim insertBeforeRow
    insertBeforeRow = 1

    ActiveSheet.Rows(insertBeforeRow).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' The following two lines are the same - both active the second sheet in the book
    Sheet2.Activate
    Sheets(2).Activate

    ActiveSheet.Rows(insertBeforeRow).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Then with a quick play with the debugger I see that the ActiveSheet has the property index - this is its index in the Sheets collection. (click ActiveSheet, right-click and select Add Watch)

I come up with this (no error-checking, don't forget to add some! - Picking the last sheet in the workbook and running this will cause the dreaded "uh-uh no you don't dummy" error sound and message box)

Sub getActiveSheet()
    Dim curSheetIndex, numSheets, secondSheetIndex

    curSheetIndex = ActiveSheet.Index
    secondSheetIndex = curSheetIndex + 1

    ' the following two lines are equivalent - both operate on the Active Sheet
    ActiveSheet.Cells(1, 1) = "Active Sheet"
    Sheets(curSheetIndex).Cells(1, 1) = "Active Sheet"

    ' finally, operate on the following sheet
    Sheets(secondSheetIndex).Cells(1, 1) = "Sheet following Active Sheet"
End Sub

You can weld the two snippets together in the manner that suits your purpose. The debugger in the VBA environment is a thing of beauty when it comes to discovering available functionality and properties. It's one of the reasons that I quite enjoy small tasks like this in VBA, to be honest. Its often quite quick 'n' easy to get the effect you're after, when at least a little familiar with the debugger and Excel's object model.

VBA code for adding a row in two sheets at one time

2 Answers