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

"Steve" wrote:

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