inserting and copying rows over multiple sheets
 

I am trying to insert and copy rows over multiple sheets, here is what
i have:


Sub SetParam()
'
' SetParam
' Number of rows and colums
Dim Rng
Rng = InputBox("Enter number of rows required.")

'Select Relevant Sheets
Sheets(Array("Variance", "Variance (C)", "Res Risk", "UtilHrs", "LTD
Hr", "WDV", _
"WDV(2)", "Lease", "INT", "Depn", "INS", "Hire", "MMR", "GM",
"Labour", "TyreTrack", _
"GET", "Lube", "bcm", "Revenue", "Op Lease Int", "Depn OP",
"Op lease", "Fuel")).Select

'insert rows
Sheets("Variance").Activate
Rows("7:7").Select

Selection.Offset(Rng - 1, 0).Select
Selection.EntireRow.Insert



'Copy Formula
Dim iLastRow As Long
Dim ilastcol As Long
ilastcol = Cells(6, Columns.Count).End(xlToLeft).Column
iLastRow = Rng + 6
Range("a6", Cells(6, ilastcol)).AutoFill Destination:=Range("a6",
Cells(iLastRow, ilastcol)), Type:=xlFillDefault

Sheets("Cashflow").Select
Range("B2").Select
End Sub


This code only inserts and copys rows to the "Variance" Sheet though,
can any suggest a fix so that is does it to all sheets?

Thanks

inserting and copying rows over multiple sheets
 

hi,
instead of Sheets("Variance").Activate try something like this.....

Dim sh As Worksheet
For Each sh In ActiveWorkbook
'your code
Next sh

regards
FSt1

" wrote:

I am trying to insert and copy rows over multiple sheets, here is what
i have:


Sub SetParam()
'
' SetParam
' Number of rows and colums
Dim Rng
Rng = InputBox("Enter number of rows required.")

'Select Relevant Sheets
Sheets(Array("Variance", "Variance (C)", "Res Risk", "UtilHrs", "LTD
Hr", "WDV", _
"WDV(2)", "Lease", "INT", "Depn", "INS", "Hire", "MMR", "GM",
"Labour", "TyreTrack", _
"GET", "Lube", "bcm", "Revenue", "Op Lease Int", "Depn OP",
"Op lease", "Fuel")).Select

'insert rows
Sheets("Variance").Activate
Rows("7:7").Select

Selection.Offset(Rng - 1, 0).Select
Selection.EntireRow.Insert



'Copy Formula
Dim iLastRow As Long
Dim ilastcol As Long
ilastcol = Cells(6, Columns.Count).End(xlToLeft).Column
iLastRow = Rng + 6
Range("a6", Cells(6, ilastcol)).AutoFill Destination:=Range("a6",
Cells(iLastRow, ilastcol)), Type:=xlFillDefault

Sheets("Cashflow").Select
Range("B2").Select
End Sub


This code only inserts and copys rows to the "Variance" Sheet though,
can any suggest a fix so that is does it to all sheets?

Thanks