Is it possible to insert a row and it will insert the same row on another tab

[zone709]

My question is is it possible to insert a row lets say I had to insert a row in row 12 on sheets called "Rate Table" Can it insert the same row 12 on another sheet called "Paypal"? I need this because sometimes ill forget to insert the row on PayPal's tab sometimes.

 

[Professor_k]

Yes this is possible - If you hold down "Ctrl" key and left click on the sheet(s) that you want to additionally include the extra row(s) upon, and perform the action to insert the extra row, that extra row will appear in same place on every sheet that you selected with "Ctrl" + left click.

Also works for columns too and other functions.

"Ctrl" key + left click allows you to select multiple sheets.

Note: Remember to deselect the additional sheet(s) (Ctrl + left click again) after adding the new row otherwise anything you type (or perform) onwards will appear in the same cell on every selected sheet!

Hope this helps,

 

[Dryver14]

if its always on the same row you could just put an = sign on the next row of the paypal sheet and select the cell on the rate table and then drag across and down (Gives 0's where there is nothing but that can be fixed if required

 

[vcoolio]

Hello Zone709,

Apart from the methods suggested by Dryver and Professor_k, a small sub routine as follows will do the task and prevent any oversights on your part:-

Sub InsertR()

Dim ws As Worksheet

For Each ws In Worksheets
     ws.Range("A12").EntireRow.Insert '----> Change the range to suit.
Next ws

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Dryver14]

Vcoolio,

whilst a vba solution may be suitable, I don't think this piece of code is it.

Who's to say there aren't 20 worksheets of which these are 2. also the op will need to remember this and if he has input a few rows when he remembers then ther could be issues.

Probably the best method would be to look at the last row of the second sheet and compare with first, then return whats missing.

Don't be put off by these suggestions but I would suggest you do when writing code is something I very rarely do and that's ask the question...What could go wrong?

 

[vcoolio]

@Dryver:

That was my first thought and this is only a guide for the OP at this point and it obviously can be adjusted if need be. Until the OP tests it and advises us further, we won't know how many sheets need to be included/excluded (if any).

So, we wait...............

Cheerio,
vcoolio.

 

[zone709]

@Dryver:

That was my first thought and this is only a guide for the OP at this point and it obviously can be adjusted if need be. Until the OP tests it and advises us further, we won't know how many sheets need to be included/excluded (if any).

So, we wait...............

Cheerio,
vcoolio.

Thanks guys for all the feedback. I will try all of these later. My issue is i need to insert rows in between rows not at the end or else i would be fine. But since im pulling data off the rate sheet from a certain row when i insert it i have to insert on other sheet or else ill loose the data going across columns for that row. i can manually do it but sometimes i forget to insert and drag down and the data in the columns is not for the right people.

 

[zone709]

Trying to think of a way to do this so I don't forget. I have to insert on current tab, but I need it to do so in the same spot on other tab (sheet) called "Lift" worksheet 3.

Trying to see the best solution. I actually change the vba code here to this but not working. The range I also added is just the range where this is going to happen in. Tryin to come up with the best solution. If I inserted on current sheet a row I choose and it happened to add it automatically on other sheet called Lift. I would never forget as I would go to that tab and see inserted row blank.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub InsertR()
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim ws As Worksheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Worksheets(3).Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   With ws[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    IfActiveSheet.Name = "Lift" Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ws.Range("A6:A65").EntireRow.Insert .[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[vcoolio]

Hello Zone709,

Try the following for your two sheets:-

Sub InsertR()

Dim ar As Variant

ar = Array("Sheet1", "Sheet2") '----> Change sheet names to suit.

For i = LBound(ar) To UBound(ar)
     Sheets(ar(i)).Range("A6").EntireRow.Insert '----> Change range to suit.
Next i

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Jaafar Tribak]

You could create a pseudo-event to automatically detect when a row(s) has been inserted or deleted from a worksheet.

Place the following code in the ThisWorkbook Module :

Option Explicit

Private Const TARGET_WORKSHEET = "Sheet1"

Private Sub Workbook_Open()
    Call Me.Names.Add("BottomCell", Range(TARGET_WORKSHEET & "!" & Cells(Worksheets(TARGET_WORKSHEET).Rows.Count, 1).Address))
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call Intercept_Rows_Add_Delete(Sh, Target)
End Sub

Private Sub Intercept_Rows_Add_Delete(ByVal Sh As Object, ByVal Target As Range)
    Dim oRange As Range, sNameRefersTo As String, errNumber As Long

    On Error Resume Next
        Set oRange = Range("BottomCell")
        errNumber = Err.Number
    On Error GoTo 0
    
    sNameRefersTo = "=" & TARGET_WORKSHEET & "!" & Cells(Worksheets(TARGET_WORKSHEET).Rows.Count, 1).Address
    
    Select Case True
        Case errNumber <> 0
            Call Worksheet_RowInsert(Sh, Target)
            Me.Names("BottomCell").Value = sNameRefersTo
        Case Me.Names("BottomCell").Value <> sNameRefersTo
            Call Worksheet_RowDelete(Sh, Target)
            Me.Names("BottomCell").Value = sNameRefersTo
    End Select
End Sub


[B][COLOR=#008000]'Pseudo_Events...
'================[/COLOR][/B]
Private Sub Worksheet_RowInsert(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = TARGET_WORKSHEET Then
[COLOR=#008000]'        Debug.Print "Rows Inserted : " & vbCr & Target.Address[/COLOR]
        Sheet2.Range(Target.Address).EntireRow.Insert
    End If
End Sub

Private Sub Worksheet_RowDelete(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = TARGET_WORKSHEET Then
[COLOR=#008000]'        Debug.Print "Rows Deleted : " & vbCr & Target.Address[/COLOR]
        Sheet2.Range(Target.Address).EntireRow.Delete
    End If
End Sub

In order for the code to take effect, the Workbook_Open event code needs to be executed first.

The code assumes that the first worksheet is Sheet1 .. Change the TARGET_WORKSHEET constant at the top of the mosule as required... The other worksheet is assumed to be Sheet2- change also as needed.