Sub sbrInsertRow(avarSheet As Variant) Dim intRow As Integer intRow=ActiveCell.Row Worksheets(avarSheet).Select Sheets(avarSheet(0)).Activate Selection.Rows(intRow).Select With Selection .EntireRow.Insert *** formatting instructions *** End With End SubavarSheet 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.
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())All avarSheets are selected. The active cell in all avarSheets became the
VB:I need to add the option of inserting rows to the protected sheet using this code. I am using an Excel 2007 file. PLEASE HELP!!Worksheet_Activate() With Me .Protect Password:="eb", UserInterFaceOnly:=True .EnableOutlining = True End With End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:this code only works because I copy data from one sheet to the next (column c on this sheet is equal to column f in the initial sheet). I do not want to keep doing this, since the sheets become filled with useless data. I would like to refer to a range on the initial sheet (column f) but do not know how to make the connection with the 'cell.EntireRow' partRange("c3:c157") Application.ScreenUpdating = False If cell.Value = "yes" Then cell.EntireRow.Hidden = True ElseIf cell.Value = "not applicable" Then cell.EntireRow.Hidden = True Else: cell.EntireRow.Hidden = False End If Next Application.ScreenUpdating = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Const strUnitsRange As String = "Roadmap_Hide" Const strOrderSheet As String = "Global" Const strUnitsRange2 As String = "Action_Plan_Hide" Sub HideRows() Dim cel As Range ThisWorkbook.Sheets.Select For Each cel In Sheets(strOrderSheet).Range(strUnitsRange).Cells If cel.Value = 0 Then cel.EntireRow.Hidden = True If cel.Value 0 Then cel.EntireRow.Hidden = False Next cel For Each cel In Sheets(strOrderSheet).Range(strUnitsRange2).Cells If cel.Value = 0 Then cel.EntireRow.Hidden = True If cel.Value 0 Then cel.EntireRow.Hidden = False Next cel Sheet1.Select End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:test() Dim lngRow As Long Dim lngCount As Long lngRow = 8 Do lngCount = Range("A" & lngRow).End(xlDown).Row - lngRow Range("F" & lngRow & ":G" & lngRow).FormulaR1C1 = "=PERCENTILE(RC[-3]:R[" & lngCount & "]C[-3], 0.95)" lngRow = Range("A" & lngRow).End(xlDown).Row + 9 If lngRow > 65536 Then Exit Do Loop End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("F1") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End SubAny advise or help you could offer would be greatly appreciated!
Option Explicit Sub InsertRowAllSheets() ' Thanks to firefytr for the code that has been adapted into this routine Dim cs As String cs = ActiveSheet.Name Dim y As Integer y = Application.InputBox("Enter the row number you wish to add", _ Type:=1) 'enter 16 to insert a new row 16, the old row _ will become 17 And all other rows push down 1 row As well. If MsgBox("Are you sure you wish to insert at row " & y & " for ALL sheets?", _ vbYesNo, "Insert row on ALL Sheets") = vbNo Then Exit Sub Application.ScreenUpdating = False Dim r As Range Dim ws As Worksheet ' On Error Resume Next 'Error handler For Each ws In ThisWorkbook.Worksheets ws.Activate Set r = ActiveSheet.Range("A" & y) If y < 7 Then Goto circumv 'Not to insert in Headers Range("A" & y).EntireRow.Insert ' code can be inserted here to copy formulas for some or all sheets in the workbook circumv: Next ws Sheets(cs).Activate Application.ScreenUpdating = True End SubPS: If it can be done so that very specific worksheets do not get the row inserted would be nice but not a big problem if that can't be done.