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
news:0D5A4061-C617-4636-8AA4-(E-Mail Removed)...
>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
>> news:4CC863D7-814B-48F1-8052-(E-Mail Removed)...
>> > 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
>>
>>
>>
|
Similar Threads
