Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
I need to insert a row on multiple adjacent sheets. I need to do it as a
group because I have a 3-D summary sheet whose reference must remain aligned. The active cell on each sheet may be different. I want to insert the row at the location of the active cell on the active sheet. Here's what I've done: Worksheets(avarSheet).Select Selection.Rows(intRow).Select With Selection .EntireRow.Insert *** formatting instructions *** End With avarSheet is a variant containing an array, which is properly populated with the names of the sheets to which the row should be added. The worksheets are successfully selected as a group. intRow contains the number of the row of the active cell on the active worksheet. When I run this code, a row is inserted only on Sheet avarSheet(0), and at the current row of Sheet avarSheet(0) plus intRow. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
Hi Hart,
The following line returns you to only one sheet selected. Worksheets(avarSheet).Select You need something like this:- Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Sheets("Sheet1").Activate 'becomes you main sheet to work on. Regards, OssieMac "HartJF" wrote: I need to insert a row on multiple adjacent sheets. I need to do it as a group because I have a 3-D summary sheet whose reference must remain aligned. The active cell on each sheet may be different. I want to insert the row at the location of the active cell on the active sheet. Here's what I've done: Worksheets(avarSheet).Select Selection.Rows(intRow).Select With Selection .EntireRow.Insert *** formatting instructions *** End With avarSheet is a variant containing an array, which is properly populated with the names of the sheets to which the row should be added. The worksheets are successfully selected as a group. intRow contains the number of the row of the active cell on the active worksheet. When I run this code, a row is inserted only on Sheet avarSheet(0), and at the current row of Sheet avarSheet(0) plus intRow. What am I doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
Thanks for the response, OssieMac. We're part of the way to a solution.
I inserted the statement Sheets(avarSheet(0)).Activate between the Worksheets... and Selection... statements in the original post. All avarSheets are selected. The active cell in all avarSheets became the active cell in avarSheet(0) offset by intRow. avarSheet(0) was the only sheet on which the row was insereted. The calling procedure assembles an array of sheets: Dim avarSheet() As Variant, i As Integer, intSheet As Integer intSheet = Worksheets.Count - 3 ReDim avarSheet(intSheet) As Variant For i = 0 To intSheet avarSheet(i) = Worksheets(i + 3).Name Next sbrInsertRow (avarSheet()) The subroutine is defined: Sub sbrInsertRow(avarSheet As Variant) If I set a breakpoint on the With statement in the original post of the subroutine, the sheets named in the array are grouped. What am I missing? "OssieMac" wrote: Hi Hart, The following line returns you to only one sheet selected. Worksheets(avarSheet).Select You need something like this:- Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Sheets("Sheet1").Activate 'becomes you main sheet to work on. Regards, OssieMac "HartJF" wrote: I need to insert a row on multiple adjacent sheets. I need to do it as a group because I have a 3-D summary sheet whose reference must remain aligned. The active cell on each sheet may be different. I want to insert the row at the location of the active cell on the active sheet. Here's what I've done: Worksheets(avarSheet).Select Selection.Rows(intRow).Select With Selection .EntireRow.Insert *** formatting instructions *** End With avarSheet is a variant containing an array, which is properly populated with the names of the sheets to which the row should be added. The worksheets are successfully selected as a group. intRow contains the number of the row of the active cell on the active worksheet. When I run this code, a row is inserted only on Sheet avarSheet(0), and at the current row of Sheet avarSheet(0) plus intRow. What am I doing wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
Hi again
Firstly I have been guilty of no reading the post properly. Again!!!. My apologies for that. However, I did some testing and it appears to work without the EntireRow. Modify as per the following:- With Selection .Insert End With I'll be interested in your result because I have no idea why it does not like the EntireRow. Your code appears to be selecting the row anyway. Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
Hi yet again,
I have had another look at this and I think that EntireRow should be with your row selection. Selection.Rows(intRow).EntireRow.Select With Selection .Insert End With Regards, OssieMac "OssieMac" wrote: Hi again Firstly I have been guilty of no reading the post properly. Again!!!. My apologies for that. However, I did some testing and it appears to work without the EntireRow. Modify as per the following:- With Selection .Insert End With I'll be interested in your result because I have no idea why it does not like the EntireRow. Your code appears to be selecting the row anyway. Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
Hi again Hart,
This has had me so intrigued that I couldn't leave it alone. This is what I have come up with. Selection.Rows(intRow).Select 'This only selects one cell. Selection.Rows(intRow).EntireRow.Select 'Selects the entire row. Rows(intRow).Select 'Selects the entire row. No 'Selection' or 'EntireRow' intRow = 10 Rows(intRow).Select With Selection .Insert End With Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on multiple sheets
You want to insert rows based on the activesheet's activecell?
Option Explicit Sub testme() Dim WksNames As Variant Dim wks As Worksheet Dim myRow As Long Dim IntRow as long IntRow = 5 'you may not want to add one. I figured that if IntRow was 0, then you'd 'want to insert after the activecell's row--but maybe you don't. myRow = ActiveCell.Row + 1 + IntRow 'my array of sheet names WksNames = Array("Sheet1", "sheet2", "sheet3") For Each wks In Worksheets(WksNames) wks.Rows(myRow).Insert Next wks End Sub HartJF wrote: I need to insert a row on multiple adjacent sheets. I need to do it as a group because I have a 3-D summary sheet whose reference must remain aligned. The active cell on each sheet may be different. I want to insert the row at the location of the active cell on the active sheet. Here's what I've done: Worksheets(avarSheet).Select Selection.Rows(intRow).Select With Selection .EntireRow.Insert *** formatting instructions *** End With avarSheet is a variant containing an array, which is properly populated with the names of the sheets to which the row should be added. The worksheets are successfully selected as a group. intRow contains the number of the row of the active cell on the active worksheet. When I run this code, a row is inserted only on Sheet avarSheet(0), and at the current row of Sheet avarSheet(0) plus intRow. What am I doing wrong? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert row on multiple sheets | Excel Discussion (Misc queries) | |||
Insert row on multiple sheets | Excel Discussion (Misc queries) | |||
Insert Columns in multiple sheets | Excel Worksheet Functions | |||
How do you insert rows into multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
Insert rows on multiple sheets | Excel Programming |