Automatically add rows/ values from a linked worksheet

J

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
D
  • Solved
Copy values multiple columns (non-adjacent) to another sheet
Replies
4
Views
242
duranimal86
D
R
  • Question
Macro to copy range to specific destination on separate sheet, if criteria is met
Replies
0
Views
462
readysetgo
R
Sean15
  • Question
Struggling with updating links from a closed worksheet.
Replies
3
Views
709
Fluff
Fluff
M
  • Solved
Automatically add value to divide by
Replies
8
Views
134
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

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
Back
Top