Hi:
I have a call log workbook that has a sheet for each employee (14). I am
looking to create a push button macro that will populate a summary page for
all sheets when H=YES.

Essentially, I want to go into the summary page and click a button and have
all of the closed out items (yes = closed out) taken out of the individual
employee worksheet and put into the summary page. Then I want all of the
individual sheets cleaned up so that there are not a lot of blank rows

Jeremy,

Try the macro below. I've assumed that your Summary page is named Summary.

HTH,
Bernie
MS Excel MVP

Sub MakeSummary()
Dim myS As Worksheet
Dim mySS As Worksheet
Dim myR As Long
Dim myC As Range
Dim myF As Range
Dim FirstAddress As String

Set mySS = Worksheets("Summary")

For Each myS In Worksheets
If myS.Name < mySS.Name Then
With myS.Range("H:H")
Set myC = .Find("YES")
If Not myC Is Nothing Then
Set myF = myC
FirstAddress = myC.Address
Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < FirstAddress Then
Do
Set myF = Union(myF, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < FirstAddress
End If
myF.EntireRow.Copy mySS.Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
myF.EntireRow.Delete
End If
End With
End If
Next myS

End Sub


"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
Hi:
I have a call log workbook that has a sheet for each employee (14). I am
looking to create a push button macro that will populate a summary page for
all sheets when H=YES.

Essentially, I want to go into the summary page and click a button and have
all of the closed out items (yes = closed out) taken out of the individual
employee worksheet and put into the summary page. Then I want all of the
individual sheets cleaned up so that there are not a lot of blank rows

I understand most of the code, but the problem I have is the delete the row
part. I have some columns that are hidden that I cannot afford to delete.
Can I protect hidden columns?

"Bernie Deitrick" wrote:

Jeremy,

Try the macro below. I've assumed that your Summary page is named Summary.

HTH,
Bernie
MS Excel MVP

Sub MakeSummary()
Dim myS As Worksheet
Dim mySS As Worksheet
Dim myR As Long
Dim myC As Range
Dim myF As Range
Dim FirstAddress As String

Set mySS = Worksheets("Summary")

For Each myS In Worksheets
If myS.Name < mySS.Name Then
With myS.Range("H:H")
Set myC = .Find("YES")
If Not myC Is Nothing Then
Set myF = myC
FirstAddress = myC.Address
Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < FirstAddress Then
Do
Set myF = Union(myF, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < FirstAddress
End If
myF.EntireRow.Copy mySS.Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
myF.EntireRow.Delete
End If
End With
End If
Next myS

End Sub


"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
Hi:
I have a call log workbook that has a sheet for each employee (14). I am
looking to create a push button macro that will populate a summary page for
all sheets when H=YES.

Essentially, I want to go into the summary page and click a button and have
all of the closed out items (yes = closed out) taken out of the individual
employee worksheet and put into the summary page. Then I want all of the
individual sheets cleaned up so that there are not a lot of blank rows

What part do you want to delete? This will move the first 8 columns....

Sub MakeSummary()
Dim myS As Worksheet
Dim mySS As Worksheet
Dim myR As Long
Dim myC As Range
Dim myF As Range
Dim myA As Range
Dim FirstAddress As String
Dim myW As Integer

myW = 8

Set mySS = Worksheets("Summary")

For Each myS In Worksheets
If myS.Name < mySS.Name Then
With myS.Range("H:H")
Set myC = .Find("YES")
If Not myC Is Nothing Then
Set myF = myC
FirstAddress = myC.Address
Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < FirstAddress Then
Do
Set myF = Union(myF, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < FirstAddress
End If
For Each myA In myF.Areas
myA.Offset(0, -7).Resize(, myW).Copy mySS.Cells(Rows.Count, 1).End(xlUp)(2)
myA.Offset(0, -7).Resize(, myW).Delete
Next myA
End If
End With
End If
Next myS

End Sub


--
HTH,
Bernie
MS Excel MVP


"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
I understand most of the code, but the problem I have is the delete the row
part. I have some columns that are hidden that I cannot afford to delete.
Can I protect hidden columns?

"Bernie Deitrick" wrote:

Jeremy,

Try the macro below. I've assumed that your Summary page is named Summary.

HTH,
Bernie
MS Excel MVP

Sub MakeSummary()
Dim myS As Worksheet
Dim mySS As Worksheet
Dim myR As Long
Dim myC As Range
Dim myF As Range
Dim FirstAddress As String

Set mySS = Worksheets("Summary")

For Each myS In Worksheets
If myS.Name < mySS.Name Then
With myS.Range("H:H")
Set myC = .Find("YES")
If Not myC Is Nothing Then
Set myF = myC
FirstAddress = myC.Address
Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < FirstAddress Then
Do
Set myF = Union(myF, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < FirstAddress
End If
myF.EntireRow.Copy mySS.Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
myF.EntireRow.Delete
End If
End With
End If
Next myS

End Sub


"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
Hi:
I have a call log workbook that has a sheet for each employee (14). I am
looking to create a push button macro that will populate a summary page for
all sheets when H=YES.

Essentially, I want to go into the summary page and click a button and have
all of the closed out items (yes = closed out) taken out of the individual
employee worksheet and put into the summary page. Then I want all of the
individual sheets cleaned up so that there are not a lot of blank rows