Sleeper: Inserting Rows and keeping the same formats and continuing the formulas.

[SHH]

I currently have a couple of macros in a workbook and I need to do one more thing with it. Currently my macro inserts the rows on each page at the same location and it keeps the same format. I need it to carry the formulas down or up depending on the line insertion. Example: I insert a new line 7, on the following pages line 7 is also inserted correctly however the information I type in line 7 on the master page is supposed to transfer to the corresponding cell on the following pages.

Any help? Feel free to email me and I will be more than happy to show you what I currently have. Thanks in advance for all the help.

Heath

[Ken Puls]

Hi Heath, welcome to the board!

Is there any way you can post the applicable code you want help with? There may not be any need to post any data at all, but that way you can get multiple experts working on it instead of just one.

Cheers,

[SHH]

Here is the specific code. I actually got the code from someone in this forum. Thanks again.

Option Explicit

Sub InsertRowAllSheets()
'  Thanks to firefytr for the code that has been adapted into this routine
Dim cs As String
    cs = ActiveSheet.Name
    Dim y As Integer
    y = Application.InputBox("Enter the row number you wish to add", _
    Type:=1) 'enter 16 to insert a new row 16, the old row _
    will become 17 And all other rows push down 1 row As well.
    If MsgBox("Are you sure you wish to insert at row " & y & " for ALL 
sheets?", _
    vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Dim r As Range
    Dim ws As Worksheet
     '    On Error Resume Next 'Error handler
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        Set r = ActiveSheet.Range("A" & y)
        If y < 7 Then Goto circumv 'Not to insert in Headers
        Range("A" & y).EntireRow.Insert
'  code can be inserted here to copy formulas for some or all sheets in the workbook
circumv:
    Next ws
    Sheets(cs).Activate
    Application.ScreenUpdating = True
End Sub

[Ken Puls]

Hi Heath,

You could try this:

Sub InsertRowAllSheets()
' Thanks to firefytr for the code that has been adapted into this routine
Dim cs As String
 cs = ActiveSheet.Name
 Dim y As Integer
 y = Application.InputBox("Enter the row number you wish to add", _
 Type:=1) 'enter 16 to insert a new row 16, the old row _
 will become 17 And all other rows push down 1 row As well.
 If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _
 vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub
 Application.ScreenUpdating = False
 Dim r As Range
 Dim ws As Worksheet
 ' On Error Resume Next 'Error handler
 For Each ws In ThisWorkbook.Worksheets
 ws.Activate
 Set r = ActiveSheet.Range("A" & y)
 If y < 7 Then GoTo circumv 'Not to insert in Headers
 With Range("A" & y)
     .EntireRow.Insert
     .Offset(-2, 0).EntireRow.Copy
     .Offset(-1, 0).PasteSpecial xlPasteAll
 End With
' code can be inserted here to copy formulas for some or all sheets in the workbook
circumv:
 Next ws
 Sheets(cs).Activate
 Application.ScreenUpdating = True
End Sub

This will copy the entire row from above, which hopefully is what you're after. Test it out (save the workbook first!) and let me know if there's any other changes required.

Cheers,

[gillybo]

How do we adjust this same code to only insert lines on specific tabs?
I'm wanting to use this same code, however when I add a line various locations in "Monthly Time Tab", I need a line to be added in the same location in the "Monthly Dollars Tab."

[SamT]

That 's not the best example to start from.

However, could you start a new thread giving the details of what you need in that thread.

You can refer back to this thread with the partial link: "vbaexpress.com/forum/showthread.php?1528-Inserting-Rows-and-keeping-the-same-formats-and-continuing-the-formulas#post369564"

I want to close this 13 yo thread.

Thank you for your forbearance.