Thread: Inserting Rows across sheets

Good Day all.
I have a sheet example that has an ALL tab were data is actualy changed in the 5 sheets that takes the data and puts it in groups of defeciant test scores. a couple of thing get messed up id you insert a row into the All tab.
1. any filters set in the other sheets go away.
2. the cell referces from the all tab get outta wack and I have to pull them down again.

Now I think I have the person doing the inputting to just add any new people to the sheet at the bottom then resort them, this will work fine but I still have to go into each sheet and re-do each filter that I have set up for each sheet.

So is there anyway to filter out the blanks in my sheets so I get the correct deficency reports per each class. This will make sence I think when you look at the workbook.any advice would be great.

See if this workbook helps. It should remove the need for filters.

Typically
With Sheet "BPD_DEF"
1/. Insert 2 columns at the start of the sheet, these will be used as helpers and can be hidden.

2/. In A2

=IF(AND(All!A2<>"",All!H2<70),ROW(C2),"")

Drag/Fill Down to match the rows in Sheet "All" and a bit more to allow for as much entries as you reasonably expect to have.

3/. In B2 this array formula

=IF(ROW()-ROW($B$2:$B$1000)+1>ROWS($A$2:$A$1000)-COUNTBLANK($A$2:$A$1000),"",INDIRECT(ADDRESS(SMALL((IF($A$2:$A$1000<>"",ROW($A$2:$A$1000),ROW()+ROWS($A$2:$A$1000))),ROW()-ROW($B$2:$B$1000)+1),COLUMN($A$2:$A$1000),4)))

Confirm with Ctrl+Shift+Enter not just Enter.
Drag Down as required.

4/. In C2

=IF($B2="","",IF(INDEX(All!A:A,$B2)="","",INDEX(All!A:A,$B2)))

Drag Across to your last required column (Column P?), then Down as required.

Repeat this for each sheet adjusting the column reference in column A to suit
i.e.
=IF(AND(All!A2<>"",All!H2<70),ROW(C2),"")

Hope this helps.

Note
I have removed all names, macros, and tidied the C/F to clarify this sample workbook.

Thank You So MUCH, This solved alot of issues. Awsome

If you look at the BPD_DEF tab Marcus isnt Deficient in anything yet it is showing he is. it should in each tab show the people deficient in that catagory but it is still catching some that are not deficient. Looking at this It happened when I added a new person then resorted the All tab. so is there another way to add a person to this and sorting it in alpha, without is messing up? I tried to insert a line too didnt fare all that well either. Man this code is awsome if I can just keep them from adding new.

Marcus doesn't show in tab "BPD_DEF" in the sample I posted.

Have you downloaded the sample and compared it with your actual workbook?

Indeed, that's the easiest way, I should have mentioned that!

What would be an easy way to automate the sort so she don't mess this up, such as a "sort button" I tried to record a macro but that didnt work, went back to my excel 2010 bible still reading lol

I have the following macro :

Sub SarahSort()
'
' Macro1 Macro
' sort alpha
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Columns("A:N").Select
    ActiveWorkbook.Worksheets("All").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("All").Sort.SortFields.Add Key:=Range("A:A"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("All").Sort
        .SetRange Range("A:N")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("O1").Select
End Sub

In 2010 I created a tab and that worked ok, however the end user is using 2007 so is there a way I can have a code that would create a tab or something in the ribbon that would allow her to execute this??

Try this workbook, it should sort as you go, no buttons required.

Add a new name at the bottom of the list, fill Column A first then Column B.

You should be able to add/delete rows in Sheet "All" as required. (Don't add/delete columns)

This code goes in the worksheet module for Sheet "All"

If you have other sheets in the workbook, you will have to exclude them from the last loop, or specify the sheets that the code has to work with.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim isect As Range
    Dim LastRow As Long
    Dim strTarget As String, strFormula As String

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    If LastRow = 1 Then Exit Sub
    
    On Error GoTo ResetApplication
    Set isect = Intersect(Target, Range("B2:B" & LastRow))
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Cells.Count = 1 Then
        strTarget = Target
        If Not isect Is Nothing Then
            Range("A2:N" & LastRow).Sort key1:=Range("A2"), Order1:=xlAscending, _
                                         key2:=Range("B2"), Order2:=xlAscending
            Range("C2:C" & LastRow).Find(What:="", After:=Range("C2")).Select
        End If
    End If
    If Not isect Is Nothing Then
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "All" Then
                With ws
                    strFormula = .Range("A2").Formula
                    .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Clear
                    .Range("A2").Resize(LastRow, 1).Formula = strFormula
                End With
            End If
        Next
    End If
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Set isect = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Hope this helps.

Try this workbook. It's protected without passwords.

If you want to use a password then use the same password for all the sheets (except sheet "All", it can have a different one).

For each sheet apply the formatting and protection as shown then apply your password.

Then in the code for the worksheet module Sheet "All" worksheet_change event.
Change these lines

                With ws
                    .Unprotect Password:=""
                    strFormula = .Range("A2").Formula
                    .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Clear
                    .Range("A2").Resize(LastRow, 1).Formula = strFormula
                    .Protect Password:=""
                End With

To

                With ws
                    .Unprotect Password:="Your Password"
                    strFormula = .Range("A2").Formula
                    .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Clear
                    .Range("A2").Resize(LastRow, 1).Formula = strFormula
                    .Protect Password:="Your Password"
                End With

I hope that she understands this, however I'm sure you'll keep her right.

Select the whole sheet in question (Not Sheet "All") then

Format > Format Cells ...
Select the Tab "Protection" and check both boxes Locked and Hidden