Automatically add rows/ values from a linked worksheet

[John Supsic]

Hi-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am brand new to Mr. Excel and would love some advice. <o></o>
<o></o>
I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.<o></o>
<o></o>
I am using Excel 2007<o></o>
<o></o>
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.<o></o>
<o></o>
Any help is greatly appreciated!<o></o>
<o></o>
John<o></o>
<o></o>

 

[namrathashanmukh]

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

 

[John Supsic]

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

 

[namrathashanmukh]

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

 

[John Supsic]

ADVERTISEMENT

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.

 

[namrathashanmukh]

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

 

[namrathashanmukh]

ADVERTISEMENT

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

 

[namrathashanmukh]

The insertion will take place everytime anything changes on sheet2.

 

[John Supsic]

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

 

[Sonny1161]

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