Automatically add rows/ values from a linked worksheet

John Supsic

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

namrathashanmukh

New Member
Joined
Apr 24, 2008
Messages
47
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

New Member
Joined
Oct 22, 2008
Messages
4
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
 

John Supsic

New Member
Joined
Oct 22, 2008
Messages
4

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

New Member
Joined
Apr 24, 2008
Messages
47
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

New Member
Joined
Apr 24, 2008
Messages
47

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
 

John Supsic

New Member
Joined
Oct 22, 2008
Messages
4
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

New Member
Joined
Dec 2, 2014
Messages
4
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,891
Messages
5,834,263
Members
430,273
Latest member
Windrunner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top