Hi,
I've currently been using a button to run the macro below to add an entire new row that keeps formulas and clears out entered data by selecting any cell and hitting the button. I'm working on another file now that will have data in columns A,B,C, D, and E that can't be changed or move down (data has to stay in it's current cells) so the current macro won't work as it moves the entire row down which would break the data in those columns.
Starting in cell F5 data will be added through AF5 and continue down each row indefinitely. I've been trying to modify this macro (with of course no luck) to have it do the same add new row function when selecting any cell from F5 and down but only add a partial row starting at column F and going across not effecting the information in columns A,B,C, D, and E.
Another aspect that is needed with the add row macro is for the new row to be inserted at the same place on 5 out of 10 sheets in the workbook. The current macro will add the row to multiple sheets if I group the sheets prior to running the macro, but I need the rows added to the different sheets without having to manually group the sheets first.
Hope I've explained it clear enough. Not sure if this is even possible, but if it is help pointing me in the right direction for a solution would be greatly appreciated... I've come up empty so far.
Thank you!Sub Macro6(Optional vRows As Long = 0) ' Documented: ' Re: Insert Rows -- 1997/09/24 Mark Hill ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Dim x As Long ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'Default for 1 row, type 1 is number If vRows = False Then Exit Sub End If 'if you just want to add cells and not entire rows 'then delete ".EntireRow" in the following line 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Long ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedSheets Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01 ' to remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select End Sub
Bookmarks