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

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
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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!!


On Jun 25, 10:39 pm, FSt1 <F...@discussions.microsoft.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -