Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Automatically add rows/ values from a linked worksheet

  1. #1
    New Member
    Join Date
    Oct 2008
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatically add rows/ values from a linked worksheet

    Hi-

    I am brand new to Mr. Excel and would love some advice.

    I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.

    I am using Excel 2007

    How do you automatically add rows and update values for cells to a linked worksheet in which rows have been added? For example: Sheet 1, columns A & B are linked to Sheet 2, columns A & B. Sheet 2 has values in A1:A5 & B1:B5 and Sheet 1, since it is linked, has the same info. I want to add a row in between 3 & 4 on Sheet 2 and want Sheet 1 to automatically add the same row and update the value of the cell in column A & B.

    Any help is greatly appreciated!

    John

  2. #2
    New Member
    Join Date
    Apr 2008
    Location
    New Jersey , USA
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    you got to initialize the workbook...
    sheet1.active


    Rows(4 & ":" & 4).Select
    Selection.EntireRow.Insert


    This is insert a line in sheet 1 at position 4 ..

    Also can you specify Wat values you wanted entered?


    Thanks,
    Namratha

  3. #3
    New Member
    Join Date
    Oct 2008
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    Can you explain in further detail what you mean by "initialize the workbook"? Will this automatically update any time a change is made on Sheet 2?

    The values will be text & or #s entered on Sheet 2.

    Thanks!

    John

  4. #4
    New Member
    Join Date
    Apr 2008
    Location
    New Jersey , USA
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    Do you the insertion to happen on changes on the sheet or on a click of a command

  5. #5
    New Member
    Join Date
    Oct 2008
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    I want Sheet 1 to automatically insert a row in the same position when a row is added on Sheet 2. Also, i want the values in specified cells to link from Sheet 2 to Sheet 1.

  6. #6
    New Member
    Join Date
    Apr 2008
    Location
    New Jersey , USA
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    Private Sub Worksheet_Change(ByVal Target As Range)

    Set sourcebook = ThisWorkbook
    Set sourcesheet = sourcebook.Worksheets("sheet2")

    Set targetbook = ThisWorkbook
    Set targetsheet = targetbook.Worksheets("sheet1")

    targetsheet.Activate
    ActiveSheet.Rows(4).EntireRow.Insert



    End Sub


    This inserts a line in sheet1 at line 4 when u insert a line in sheet2 at line 4


    Thanks,
    Namratha

  7. #7
    New Member
    Join Date
    Apr 2008
    Location
    New Jersey , USA
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    Private Sub Worksheet_Change(ByVal Target As Range)

    Set sourcebook = ThisWorkbook
    Set sourcesheet = sourcebook.Worksheets("sheet2")

    Set targetbook = ThisWorkbook
    Set targetsheet = targetbook.Worksheets("sheet1")
    If targetsheet.Cells(4, 1).Value = "" Or targetsheet.Cells(4, 2).Value = "" Then
    GoTo link
    Else
    GoTo insertion
    End If

    insertion: targetsheet.Activate
    ActiveSheet.Rows(4).EntireRow.Insert

    sourcesheet.Activate
    link:
    targetsheet.Cells(4, 1) = sourcesheet.Cells(4, 1).Value
    targetsheet.Cells(4, 2) = sourcesheet.Cells(4, 2).Value


    End Sub


    This does what you need... Let me know if this is what you needed.

    THanks,

    Namratha

  8. #8
    New Member
    Join Date
    Apr 2008
    Location
    New Jersey , USA
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    The insertion will take place everytime anything changes on sheet2.

  9. #9
    New Member
    Join Date
    Oct 2008
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    It is getting close but not quite there. The most up to date macro provided only runs if I remove "ByVal Target As Range" in the Sub line. Is that correct or is there another way to run the macro with the "ByVal Target As Range" command?
    After this is removed the macro adds a new row on Sheet 1, row 4 with the values of Sheet 2, row 4 every time it runs. The problem is it continues to add rows with the same values on Sheet 2, row 4 every time I run the macro.
    I want the macro to be able to update any row on Sheet 1 in which there was a change on Sheet 2, not just row 4 (I originally provided row 4 as an example for what I want the macro to do).

    Let me know if you need me to be more specific.

    Thanks so much for your help thus far!
    John

  10. #10
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically add rows/ values from a linked worksheet

    Hi There,
    Like John, I am also trying to add cells/rosws to another sheet automatically when I add rows to a master sheet. Will your formula adjust based on the number of cells entered into the master sheet?

    Thanks to both of you for letting me chime in with my question.
    Sonny

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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