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