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
– Wayne G. Dunn, Commented Mar 4, 2017 at 17:41
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
– user3598756, Commented Mar 4, 2017 at 17:52
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
– Ryan, Commented Mar 4, 2017 at 18:20

user3598756 · Accepted Answer · 2017-03-04 19:57:16Z

0

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

answered Mar 4, 2017 at 19:57

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3598756 Over a year ago

@Ryan, did you get through it?

Ryan Over a year ago

Sorry for the late reply. This works excellent on the items itself, but my stock information doesnt move a row down. If i extend the range to row I my Stock information on and below the row inserted disappears.

Ryan Over a year ago

Any ideas user3598756? Thanks in advance.

Collectives™ on Stack Overflow

Excel VBA Insert/delete Row in Sheet

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related