summary page

[jdgibson13]

Hi all,
I've found things similar and I keep having problems mapping them to my exact situation.
I'm trying to create a "summary page". I have a bunch of different sheets, and in those sheets column J is a drop down box. So if column J doesn't equal "approved" and it isn't blank, I want to copy the whole row and paste it in the next available row on my summary sheet. This will give me a list of all line items that need work (and eventually I'll extend this to be a list of all the line items with certain tags on them, like user name). I'll tie this macro to a button that says "update sheet" and completely wipes my summary sheet and re-fills in the information.

Any help would be great,
Thanks

 

[Gingertrees]

Caveat: a simple data-val drop down will allow this to work, but other code in that cell could interfere (for example, an if-then statement causes this to work incorrectly).
I am assuming your summary page will be on a sheet titled "master"; I am also assuming your dropdown is in cell J1 on your sheets; tailor as needed:

Sub SUMMARYOFSHEETS()
Dim master As Worksheet, Lastrow As Integer, i As Integer
Dim LastCol As Integer
Application.ScreenUpdating = False
Set master = Sheets("master")
Lastrow = master.UsedRange.Rows(master.UsedRange.Rows.Count).Row
LastCol = master.UsedRange.Columns(master.UsedRange.Columns.Count).Column

If MsgBox("Are you sure? This will clear all data from Master sheet.", _
vbOKCancel) = vbCancel Then Exit Sub
'//you want to be sure you didn't click the button by accident

master.Range(Cells(2, 1), Cells(Lastrow, LastCol)).Select
Selection.EntireRow.Delete shift = xlUp
'//clears all below a header row - if you want all gone, change it to Cells(1,1)

Range("A2").Select


For i = 2 To ThisWorkbook.Sheets.Count
    Sheets(i).Select
    If Sheets(i).Name = "master" Then
    Else:
        If Sheets(i).Range("J1").Text <> "Approved" Then
            If Sheets(i).Range("J1") <> "" Then
                Rows("1").EntireRow.Copy
                master.Activate
                Lastrow = master.UsedRange.Rows(master.UsedRange.Rows.Count).Row
                Range("A" & Lastrow + 1).Select
                Selection.PasteSpecial xlPasteAll
                Range("A" & Lastrow + 1).Select
                Application.CutCopyMode = False
            End If
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[jdgibson13]

Small misunderstanding, my fault.
Every row of column j contains a dropdown box and I need to select each of those that aren't selected as "approved".
So if rows 2-6 are "Approved" and row 7 isn't I would like to select row 7 and copy it to the summary sheet.

 

[Gingertrees]

Try this (untested, but should work):

Like this:

Sub SUMMARYOFSHEETS()
Dim master As Worksheet, Lastrow As Integer, i As Integer
Dim LastCol As Integer, j as integer, lastrowSH as integer
Application.ScreenUpdating = False
Set master = Sheets("master")
Lastrow = master.UsedRange.Rows(master.UsedRange.Rows.Count).Row
LastCol = master.UsedRange.Columns(master.UsedRange.Columns.Count).Column

If MsgBox("Are you sure? This will clear all data from Master sheet.", _
vbOKCancel) = vbCancel Then Exit Sub
'//you want to be sure you didn't click the button by accident

master.Range(Cells(2, 1), Cells(Lastrow, LastCol)).Select
Selection.EntireRow.Delete shift = xlUp
'//clears all below a header row - if you want all gone, change it to Cells(1,1)

Range("A2").Select


For i = 2 To ThisWorkbook.Sheets.Count
    Sheets(i).Select
    If Sheets(i).Name = "master" Then
    Else:
        LastrowSH = Activesheet.UsedRange.Rows(Activesheet.UsedRange.Rows.Count).Row
        For j=1 to LastrowSH
            If Sheets(i).Range("J" & j).Text <> "Approved" Or _ 
            Sheets(i).Range("J" & j)<>"" Then
                Rows(j).EntireRow.Copy
                master.Activate
                Lastrow = master.UsedRange.Rows(master.UsedRange.Rows.Count).Row
                Range("A" & Lastrow + 1).Select
                Selection.PasteSpecial xlPasteAll
                Range("A" & Lastrow + 1).Select
                Application.CutCopyMode = False
            End If
        Next j
    End If
Next i
Application.ScreenUpdating = True
End Sub