Excel VBA Insert/delete Row in Sheet

Question

I have an Excel Workbook for Inventory tracking and can't seem to make it work just as I need it. I have a Master Sheet with the Product and then Multiple Sheets for Warehouse locations. I would like to be able to add a new row in my master sheet and update the other sheets with the new product. I tried to write a VB Script to copy the part number from master to warehouse main and then populate the list with vlookup. I chose this approach so that when I change the description in master it would update all the sheets. I can't seem to be able to write a VB script that inserts a row where I want it to and do the same in my other sheets.

Here is an example:

Code I used to add rows from Master to Sub-sheet:



     Private Sub Worksheet_Change(ByVal Target As Range)
       Application.ScreenUpdating = False
        For a = 1 To 10 
            Sheets("Master").Rows(a).Copy
            Sheets("VBtest").Rows(a).Insert Shift:=xlDown
        Next a
        Application.ScreenUpdating = True
        End Sub

Problem is it removes all my stock information. Just trying to add blank row and copy part number in first cell of row doesn't seem to work for me.

Is that example of how you want it to be, or how it looks with your code? Please post your code so we can take a look. — Wayne G. Dunn
just link "Warehouse Main" and "Warehouse Remote" sheets cells content to those of corresponding "Master" ones with such a plain formula as ='Master '!A2 to be written in ranges A2:F1000 (or whatever the maximum row size you need) — user3598756
This works until i add a new row in master, the extra columns in sub-sheets remain but A:F get updated. The description for the items will change with your approach but it will not move the stock information down to match old description. — Ryan

user3598756 user3598756 · Accepted Answer · 2017-03-04T19:57:16

add in "Master" sheet code pane the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("A" & Target.row & ":F" & UsedRange.Rows(UsedRange.Rows.count).row)
        Worksheets("Warehouse Main").Range(.Address).Value = .Value
        Worksheets("Warehouse Remote").Range(.Address).Value = .Value
    End With
End Sub

Excel VBA Insert/delete Row in Sheet

1 Answers