Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
copying formulas when inserting new rows | Excel Discussion (Misc queries) | |||
Inserting multiple rows and copying formulas | Excel Discussion (Misc queries) | |||
need help please inserting multiple rows based on cell value then copying to data sheet | Excel Worksheet Functions | |||
Copying header rows and inserting them into all worksheets | Excel Programming |