Code to Insert rows and copy formulas

[Steve]

Hi all. I am trying to insert a row into multiple sheets, and copy
the formula from the row above into the newly inserted row. The row
of formulas I am copying are different on each sheet, but the row I am
inserting is the same row number on each sheet. When I do this
manually (not through code - essentially highlighting the tabs within
the workbook, and inserting a single row on the Data sheet, then
copying the formulas) it works perfectly. But when I run the code, it
only inserts a row and copies the above formulas in the Data sheet,
NOT the array of sheets. Any ideas how I can edit the below code?
Thanks so much!!

-Steve


Sub New_Project()

Dim ws As Worksheet
Dim x As Integer

x = InputBox("How many rows do you want to insert?")

Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next

Range("B5000").End(xlUp).Select

Sheets(Array("Data", "Data2", "Data3", "Calc", "Summary", "PandL", _
"COGS Calc", "Rev Calc", "Revenue", "Transactions")).Select
Sheets("Data").Activate

ActiveCell.Offset(1, 0).Resize(x, 1).EntireRow.Insert
ActiveCell.Offset(-2 - x, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Resize(x, 1).EntireRow.PasteSpecial
Paste:=xlFormulas
Application.CutCopyMode = False
Selection.End(xlToLeft).Select

Sheets("Data").Select

Application.ScreenUpdating = True

End Sub

 

[Guest]

hi
I'm playing with your code. Problems.
1. the array is not needed. i commented it out. serves no purpose.
2. I moved the next statement to the bottom so that the code would loop
through all the worksheets and perform the same on each sheet.
3. when i ran the code as you wrote, the rows were inserted BELOW all the
data and the paste was pasteing over other data.
4. i had to "play" with the Offsets to line up the add row and paste but i
think i am now off your standard row.

what is the row number that is the same on each sheet?

I have got the code working but i need the row number to complete.

Regards
FSt1

 

[shah shailesh]

Try this,

Sub New_Project()
Dim ws As Worksheet
Dim x As Integer
x = InputBox("How many rows do you want to insert?")
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Visible = xlSheetVisible
Next
Sheets(Array("Data", "Data2", "Data3", "Calc", "Summary", "PandL", _
"COGS Calc", "Rev Calc", "Revenue", "Transactions")).Select
Sheets("Data").Activate
Range("B5000").End(xlUp).Offset(1, 0).Resize(x, 1).Insert
Range("B5000").End(xlUp).EntireRow.Copy
Range("B5000").End(xlUp).Offset(1, 0).Resize(x, 1).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)
If You Can't Excel with Talent, Triumph with Effort.

 

[Steve]

Hi. Thanks for the response. The "same row" on each sheet is
dynamic. What I want it to do is look at the sheet "Data", and do and
end-up to find the last row of data. Then offset 1 row and insert a
line. In the calculation sheets where I want formulas copied, I also
have Sum's. So I can't just add data to the bottom of the data
sheet...I need to Insert to make sure all my formulas (there are a lot
that reference the sheets) block down a row as well. So, when I said
same row, I meant if I am inserting on row 35 on the data sheet, I am
inserting on row 35 on all sheets. Thanks again!!