I have a workbook with 15 worksheets. The first 9 sheets contain
lists of documents (by document number, with author, dates, title,
etc.) The other 6 sheets are support sheets. I need a macro that
will examine Column A's data of each Row of each of the first 9 sheets
(for grouping purposes, not all Rows have data on some sheets). For
each Row that does have data in Column A, the macro must then examine
Column E of that Row for data. If that Cell in Column E is empty,
that entire Row must be copied to the next available Row on the Last
Sheet. If that cell in Column E has data, ignore that Row and move on
to the next Row.

I have created some limited macros in Excel 95, but this VBA in Excel
2000 has me stumped as I can't get the two loops (with testing) to
work with the row copying. Thanks for ANY guidance...

One way:

Public Sub CopyBlankEs()
Dim destRange As Range
Dim cell As Range
Dim i As Integer
Set destRange = Worksheets(Worksheets.Count).Cells( _
Rows.Count, 1).End(xlUp).Offset(1, 0)
For i = 1 To 9
With Worksheets(i)
For Each cell In .Range("A1:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
With cell
If Not IsEmpty(.Value) Then
If IsEmpty(.Offset(0, 4).Value) Then
.EntireRow.Copy destRange
Set destRange = destRange.Offset(1, 0)
End If
End If
End With
Next cell
End With
Next i
End Sub



In article ,
(Tom Gerber) wrote:

I have a workbook with 15 worksheets. The first 9 sheets contain
lists of documents (by document number, with author, dates, title,
etc.) The other 6 sheets are support sheets. I need a macro that
will examine Column A's data of each Row of each of the first 9 sheets
(for grouping purposes, not all Rows have data on some sheets). For
each Row that does have data in Column A, the macro must then examine
Column E of that Row for data. If that Cell in Column E is empty,
that entire Row must be copied to the next available Row on the Last
Sheet. If that cell in Column E has data, ignore that Row and move on
to the next Row.

I have created some limited macros in Excel 95, but this VBA in Excel
2000 has me stumped as I can't get the two loops (with testing) to
work with the row copying. Thanks for ANY guidance...

--
Email address ROT-13'd for spam reduction
see www.mcgimpsey.com/excel/groupspam.html to decode

Thanks for the example. I looked at it and understood the logic, so I
cut & pasted it into a new module of my workbook, stepped thru it, it
seemed to work OK, hit the run button and Viola! It produced the list
just like I needed! But, it has only run properly that one time. Is
there a "magic phrase" I need to say to get it to run again? (I have
uttered several phrases since!) The code executes without displaying
errors, but does not copy any rows to the last sheet, which remains
empty. Why would it run great only once?

Tom




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No idea - it works multiple times for me in a test workbook.

Has your data changed in between runs?

do you have any other macros running?

Step through the code again - where is it acting unexpectedly?

In article ,
TAGyurit wrote:

Thanks for the example. I looked at it and understood the logic, so I
cut & pasted it into a new module of my workbook, stepped thru it, it
seemed to work OK, hit the run button and Viola! It produced the list
just like I needed! But, it has only run properly that one time. Is
there a "magic phrase" I need to say to get it to run again? (I have
uttered several phrases since!) The code executes without displaying
errors, but does not copy any rows to the last sheet, which remains
empty. Why would it run great only once?