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>

 

[Tet Htut Naing]

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

Hi Namratha,
Thanks for your Macros,
It really works for me as I change each 4 as 1 and it works.
But one more thinking, the original questioner wants Whatever changes in selected rows and columns of Sheet2, then it makes the same changes of those rows and columns of sheet1.
If insertion a row at Number 10 in sheet2, it will insert a row of that position in sheet1.
How about deletion?
How about copying formula from the above cells if there is one?

I really appreciate your answers on this post as you show Sheet linkages. Me too, looking for the answers for it. If you reply with answers, It would be really grateful. Thanks in advance.
Htut

 

[Arctic601]

Well after sometime of lurking, I finally joined because I can't figure this very same thing out. Most likely because I have very little (basically none) macro experience or understanding.

I am trying to link a spreadsheet titled "WK1", "WK2", "WK3" and so on together so that when I add a line in "WK1" it automatically updates all other "WK2" and "WK3" sheets. I tried copying the code posted by namrathashanmukh, but clearly copied it wrong or inserted it wrong.

Thanks everyone for any potential help.

 

[Tet Htut Naing]

Hi,
I have some possible answers for this. Let me clarify things firstly that you want to add a row in WK1 and then automatically add the same position row in other sheets, WK2 and WK3. Then do you want to copy of the formula from above cells, so that if WK1 is updated in new inserted row, it will also update in WK2 and WK3.

Try this code,

Option Explicit

'>>>> Modify these names to suit your sheet names <<<<
Const sSHEET1NAME = "WK1"
Const sSHEET2NAME = "WK2"
Const sSHEET3NAME = "WK3"

'--------------------------------------------------------
Sub InsertLine()
' Insert a line in all Staff worksheets and copy formulas
'
    Dim rR As Range
    Dim lR As Long
    Dim wsWS As Worksheet
    Dim mbaAnsw As VbMsgBoxResult
    
    lR = ActiveCell.Row
    mbaAnsw = MsgBox("Do you want to insert a line above " & " current position?", _
                    Buttons:=vbOKCancel + vbQuestion, _
                    Title:="Insert New Line")
                
    If mbaAnsw = vbOK Then
        For Each wsWS In Worksheets
            With wsWS
                ' only insert in the four staff sheets
                If .Name = sSHEET1NAME Or .Name = sSHEET2NAME Or _
                        .Name = sSHEET3NAME Then
                    ' insert a row
                    .Cells(lR, 1).EntireRow.Insert
                    
                    If lR > 4 Then  ' copy formulas from row above
                        .Range(.Cells(lR - 1, 1), .Cells(lR, 5)).FillDown
                        For Each rR In .Range(.Cells(lR, 1), .Cells(lR, 5))
                        If Not rR.HasFormula Then rR.ClearContents
                       Next rR
                   End If
                 End If
            End With
        Next wsWS
    End If
End Sub

For you are successful to insert rows, deletion is not difficult, If requested, I can post here.

Best Regards,
Ko Htut

 

[Arctic601]

Thank you Tet Htut Naing for your quick reply. I didn't even think about deleting rows, but yeah I would need that to work as well, so glad you thought of that, Thank you!

Unfortunately, as I said I'm pretty new to macros and clearly struggling here. I tried what you gave me and I'm clearly doing something wrong. Below is what I copied into "This worksheet" in the Microsoft Visual Basic Editor screen in excel and while the macro runs and asks "Do you want to insert a line above current position?" it doesn't actually insert a line. Any ideas as to what I may be doing wrong? Thank you again for your help on this matter.

Everything below in orange was inserted....

Sub InsertLine()
' Insert a line in all Staff worksheets and copy formulas
'
Dim rR As Range
Dim lR As Long
Dim wsWS As Worksheet
Dim mbaAnsw As VbMsgBoxResult

lR = ActiveCell.Row
mbaAnsw = MsgBox("Do you want to insert a line above " & " current position?", _
Buttons:=vbOKCancel + vbQuestion, _
Title:="Insert New Line")

If mbaAnsw = vbOK Then
For Each wsWS In Worksheets
With wsWS
' only insert in the four staff sheets
If .Name = WK1 Or .Name = WK2 Or _
.Name = WK3 Then
' insert a row
.Cells(lR, 1).EntireRow.Insert

If lR > 4 Then ' copy formulas from row above
.Range(.Cells(lR - 1, 1), .Cells(lR, 5)).FillDown
For Each rR In .Range(.Cells(lR, 1), .Cells(lR, 5))
If Not rR.HasFormula Then rR.ClearContents
Next rR
End If
End If
End With
Next wsWS
End If
End Sub


End Insert

 

[Tet Htut Naing]

Well,
You should work on inserting new module in the insert tab of menu bar. Then put the above code in that newly created module. Again you should check the worsheet names, WK1, etc., as they should be the exactly same.
Importantly, you just skipped "Option Explicit" parts, that is the very first part of my code! Please find that in ththe code above I gavegave.
Good luck!
Best Regards,
Tet Htut Naing

 

[Arctic601]

Well I feel dumb for leaving off the "option explicit" part of the code, for some dumb reason I thought I didnt need that, again I'm entirely new to macros.

So once I put that in just like you said to do, it worked, imagine that Thank you so much!

How would I go about reversing this like you mentioned earlier to delete a row?

And now its time to go learn macros...

 

[Tet Htut Naing]

I did reply about the deletion macros, but unfortunately it was not posted properly, cos replied it by my mobile phone. So please wait for the answer till monday.
Best Regards,
Htut

 

[Arctic601]

no problem.

 

[Tet Htut Naing]

no problem.

Ok, Have a look at the below code.

'--------------------------------------------------------
Sub DeleteLine()
' Delete a line in all staff worksheets
'
    Dim rR As Range
    Dim lR As Long
    Dim wsWS As Worksheet
    Dim mbaAnsw As VbMsgBoxResult
    
    lR = ActiveCell.Row
    mbaAnsw = MsgBox("Do you want to delete current row?", _
                    Buttons:=vbOKCancel + vbQuestion, _
                    Title:="Delete current row ")
                
    If mbaAnsw = vbOK Then
        For Each wsWS In Worksheets
            With wsWS
                ' only delete from the three sheets
                If .Name = sSHEET1NAME Or .Name = sSHEET2NAME Or _
                        .Name = sSHEET3NAME Then
                    .Cells(lR, 1).EntireRow.Delete
                End If
            End With
        Next wsWS
    End If
End Sub

You should put this code right under the code of insertion. This time you won't need "Option Explicit" part. Just copy this code from the beginning and paste it.

Good luck to you!!
Htut

 

[Arctic601]

Here I am reviving an old thread. I have been using this macro that Htut provided but was wondering how I would expand further on it. Right now when you run the macro and insert a new line the formula that appears in the new line is that of the line below it. Yet in the other spreadsheets that are being updated when you run this macro, they all create a new line and the formulas adjust accordingly. A better example is below.

If you have something like this below,

Line 1 formula is A1+B1
Line 2 formula is A2+B2

when you try to add a third line between line 1 and 2, the new line's formula is A2+B2 and the line that use to be line 2 (but is now 3) is also A2+B2 instead of A3+B3.

Any ideas?

Thanks,