How do I insert rows in the same place across multiple sheets?

Question

I have an excel sheet which on one master tab I have the list of names of companies which is then referenced by 34 sheets covering various details about the company. In the 34 specific sheets data is entered and then pulled back into the master tab.

A flow chart for reference:

Master(Names) -> 34 Subsheets Subsheets(Information) -> Mastersheet

With how I have laid this out I am having trouble finding a way to add now companies to the master list, as the cells being referenced on the subsheets do not automatically get moved down. This results in things being misreferenced.

The solution I am interested in but don't know how to do is to create a macro in VBA that is always running and will add/remove a new row to all the subsheets in the exact same place it is added to the main sheet.

Ex,, If on the main sheet a new row is added between rows 50 and 51, then on all the subsheets a new row will be added as well.

I am completely lost as to how to do this, so any and all help would be appreciated. Thank you, and have a nice day!

I'm not even sure where to begin with this I apologize.

Edit: I have since come up with a solution to this problem. which is posted below, in case someone else has this same question later on.

Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)

    Dim ws
    Dim trgtRow
    Dim NewRowCount As Long
    Dim OldRowCount As Long

    trgtRow = Target.Row

    OldRowCount = Sheet3.UsedRange.Rows.Count
    NewRowCount = Sheet2.UsedRange.Rows.Count

    If Not Application.Intersect(Range("A2:A100"), Target) Is Nothing Then
        For Each ws In ThisWorkbook.Worksheets
            ElseIf ws.Name <> Sheet2.Name Then
                If OldRowCount < NewRowCount Then
                    ws.Rows(trgtRow).Insert Shift:=xlDown
                ElseIf OldRowCount > NewRowCount Then
                    ws.Rows(trgtRow).Delete Shift:=xlUp
                End If
            End If
        Next ws
    End If

End Sub

Sorry but this sounds like a difficult way to manage your data, and you'd be better off digging *out* of the hole and not deeper in. — Tim Williams, Jun 18 '19 at 17:15
https://stackoverflow.com/questions/7479721/determine-whether-user-is-adding-or-deleting-rows — Tim Williams, Jun 18 '19 at 20:43

GMalc · Accepted Answer · 2019-06-18T20:46:03.010

0

This code is just a starting point: when you insert a row in the Master worksheet, it will insert a row in each worksheet at the same place. Place the code in the worksheet object.

Down side, it you make any change at all to a cell in the target range, it will insert a new row.

Public Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, trgtRow As Long
trgtRow = Target.Row

    If Not Application.Intersect(Range("A2:A100"), Target) Is Nothing Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Master" Then ws.Rows(trgtRow).Insert Shift:=xlDown
        Next ws
    End If

End Sub

Another way to accomplish this is to; Insert the below macro in the vba window, in either ThisWorkbook or Sheet("Master"), then add a button on the Master worksheet, right click the button and assign the macro to the button. When you place your curser on a cell and select the button, the macro will insert a new row in every worksheet.

Sub InsertRowsInAllWS()
Dim ws As Worksheet, trgtRow As Long
trgtRow = ActiveCell.Row

    For Each ws In ThisWorkbook.Worksheets
        ws.Rows(trgtRow).Insert Shift:=xlDown
    Next ws

End Sub

edited Jun 18 '19 at 20:46

answered Jun 18 '19 at 17:58

GMalc

2,608
1
9
16

If a row is inserted (or deleted) then `Target.Columns.Count = Me.Columns.Count` will be `True` – Tim Williams Jun 18 '19 at 19:02
@TimWilliams can you elaborate, sorry my 2 brain cells are not connecting. – GMalc Jun 18 '19 at 19:15
If you don't want to insert a new row for every change in ColA, but only for inserted rows, then you can check to see if `Target` represents a complete row or not. – Tim Williams Jun 18 '19 at 19:22
Thank you so much! This works perfectly! I just needed to Dim ws – Richard J Jun 18 '19 at 19:49
Richard, @TimWilliams brought up a valid point. I'm not sure how to incorporate it yet, but I'm going to play around and see if I can get it to work. – GMalc Jun 18 '19 at 20:09
Thank you for the update! I really do appreciate you looking into this, I'm fairly new to using VBA and this was driving me crazy! – Richard J Jun 18 '19 at 21:05

How do I insert rows in the same place across multiple sheets?

1 Answers1