+ Reply to Thread
Results 1 to 2 of 2

update target sheet with rows as they are added or deleted to master sheet

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    excel 2013

    update target sheet with rows as they are added or deleted to master sheet

    The following was provided in response to my original request which was to have rows automatically added or deleted on my "target" sheet when added or removed from my "master" sheet and have the formulas automatically copy down on the "target" tab from the row above when rows are inserted.

    The following works if only one row is added or deleted....can this be modified or amended to work if multiple rows are added or deleted in the "source" sheet. There are multiple tabs in the workbook but the two tabs involved here are: "Master" (Source tab) and "Target" (Tab that needs to update as the master is modified).

    Like I stated, if only one row at a time is added or deleted, the code works. However, I could need to add or delete multiple rows at a time in a group....ie rows 5-8 might be deleted all at once. There could be up to 50 rows added or deleted together.

    Option Explicit
    Public iRow As Integer

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws2 As Worksheet: Set ws2 = Sheets("Target")

    If Selection.Rows.Count = 1 And Selection.Columns.Count = Columns.Count Then
    If iRow > ActiveSheet.UsedRange.Rows.Count Then
    ElseIf iRow < ActiveSheet.UsedRange.Rows.Count Then
    ws2.Rows(Target.Row - 1).Resize(2).FillDown
    MsgBox ("Uh-oh. The hamster must be on break because something went horribly wrong.")
    Exit Sub
    End If
    End If

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    iRow = ActiveSheet.UsedRange.Rows.Count

    End Sub

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    New Delhi,India
    MS-Off Ver
    Excel 2013

    Re: update target sheet with rows as they are added or deleted to master sheet

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    If you like someone's answer, click the star to give them a reputation point for that answer...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 09-17-2013, 12:57 PM
  2. Can't import txt file after macro deleted target sheet once
    By obgle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 12:54 PM
  3. [SOLVED] Automatically update the contents added on Sheet 1 to Sheet 2 & 3
    By lifeisaspreadsheet in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-22-2012, 10:22 PM
  4. Update corresponding cells on master sheet when data entered to filtered sheet
    By Sumatied in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-18-2011, 09:50 PM
  5. Replies: 8
    Last Post: 02-27-2009, 07:38 AM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1