PDA

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



SHH
12-29-2004, 03:05 PM
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
12-29-2004, 04:26 PM
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
12-30-2004, 08:20 AM
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
12-30-2004, 09:39 AM
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
09-18-2017, 12:24 PM
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
09-18-2017, 01:03 PM
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.