c001m4n
11-21-2011, 12:52 PM
I am new to this forum and I'm here seeking your expert help.
I am using excel 2007
I am looking to create a macro for users to insert a new row where they specify and will copy only the format and formula from the above row.
This new row will be inserted at the same row number across several, but not all worksheets.
Worksheet set up: ie. DataA, DataB, Chart1, DataC. I want to insert a new row into DataX sheets.
I have a really basic programming knowledge. I can read the code and understand most of it, but I do not know how to write the code.
This is the code I found on this site which does some of what I am looking for:
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
Except I do not know what to modify to target the new row into the separate worksheets, nor what copy and paste code to enter.
I am using excel 2007
I am looking to create a macro for users to insert a new row where they specify and will copy only the format and formula from the above row.
This new row will be inserted at the same row number across several, but not all worksheets.
Worksheet set up: ie. DataA, DataB, Chart1, DataC. I want to insert a new row into DataX sheets.
I have a really basic programming knowledge. I can read the code and understand most of it, but I do not know how to write the code.
This is the code I found on this site which does some of what I am looking for:
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
Except I do not know what to modify to target the new row into the separate worksheets, nor what copy and paste code to enter.