Ah-HA! Thank you Mike. I had to have data in column A in order to start the
process. This works perfectly!! Thanks so much for putting some time into
this.

jfree223

"Mike H." wrote:

The other thing you can't have, based on the way I wrote this macro, is any
row with nothing in Col A that is in row 9 or below. If it finds an empty
cell in Col A it stops looking for data below that point. Is this okay?

"JFREE223" wrote:

Thanks Mike. I don't get any errors, but I'm not seeing anything happen
either... except it navigates to row 9 upon selecting the button. I put the
macro in module 4 and assigned it to the button. Is the button supposed to
move the closed items as well as print?

I renamed the worksheet accordingly, so the code should recognize it. Not
sure what I've done incorrectly at this point. Any recommendations?

Thanks
jfree223

"Mike H." wrote:

This code would work and only one button needed. Then just have the PL hit
the Print to printer button. Be sure to test it without saving and make sure
all data is okay before putting into production. I AM deleting rows...

Sub Doit()
Dim Fnd As Double
Dim dataarray(500, 7) As Variant
Dim X As Double
Dim LastRow As Double

Application.ScreenUpdating = False
Application.Cursor = xlWait
Application.EnableEvents = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With


Sheets("OpenStuff").Select '<--Change name to the open status items sheets
X = 9
Do While True
If Cells(X, 1).Value = Empty Then
LastRow = X + Fnd
Exit Do
End If
If Cells(X, 3).Value = "Closed" Then
Fnd = Fnd + 1
For Y = 1 To 7
dataarray(Fnd, Y) = Cells(X, Y).Value
Next
Rows(X & ":" & X).Select
Selection.Delete Shift:=xlUp
GoTo SkipIncrement 'can't increment as we just deleted a row
End If
X = X + 1
SkipIncrement:
Loop

For X = 1 To Fnd
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
For Y = 1 To 7
Cells(9, Y).Value = dataarray(X, Y)
Next
Next

Dim PrtRng As Range
Set PrtRng = Range(Cells(1, 1), Cells(LastRow, 7))
With ActiveSheet.PageSetup
.Zoom = False
.PrintArea = PrtRng.Address
'.PrintTitleRows = "$1:$1" 'may not need this stuff!
'.Orientation = xlLandscape
'.FitToPagesWide = 1
'.FitToPagesTall = 10
End With

Application.ScreenUpdating = True
Application.Cursor = xlDefault
Application.EnableEvents = True
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
Cells(9, 1).Select


End Sub


"JFREE223" wrote:

That is extremely gracious of you...you just made a rough day (week) better.
I think the button to move closed items to the top is a great idea. I'll
provide an outline below of what the sheet looks like...

Worksheet name = ANF Action Items
Row 1 = header for the columns
Row 2-7 = ongoing action items that remain throughout the project. (never
close)
Row 8 = Blank
Row 9+ = action item rows (we would want all closed items to move to this
row and push everything else down respectively)

Column A = Action Items
Column B = Assigned (this is a validation list with names)
Column C = Status (Open, In-Progress, Closed, On-hold, On-going)
Column D = Meeting (details which type of meeting)
Column E = Date Opened (calendar pop-up)
Column F = Date Due (calendar pop-up)
Column G = Overdue? (this displays overdue when the date has passed)

Thank you Mike for the extra effort.

jfree223

"Mike H." wrote:

That helps a whole bunch. I will work up some code for you later this
afternoon but a few tidbits would help. What column is the status in. When
the PL goes to print his report, before printing, he should click the button
to move the stuff right? How about having a second button for the printing
that would also do moving for any closed items that didn't "float" to the
top. Would that be all right? We could move the stuff right after the
status is changed but I think that might confuse the users who do the status
changing so I like the approach of the button. How many columns is there
data on?

"JFREE223" wrote:

I'm certainly not explaining it in the best manner. Let me provide a
scenario. We have a meeting every morning which includes a discussion about
open action items, as well as closed action items. The spreadsheet has two
worksheets on it...one labeled 'open action items' and the second labeled
'Closed action items.' The spreadsheet contains calendars, conditional
formatting, etc... There is a status column on the open items worksheet that
has a validation list of "open, closed, in-progress, on-hold." The user
selects the status. I've included a button at the top of the sheet that has
code behind it to move the selected row to the "closed action item"
worksheet, but the user must press this to move any closed items off this
sheet. Before this is moved to the closed worksheet, the project leader would
like to review the closed item(s) on the open action item spreadsheet...for
meeting review/status purposes. Because the closed items vary in location
throughout the list, he would like them all grouped in one spot at the top of
the page. So what I need to do is have a row move to a designated row (row 8)
each time the user selects "closed" in the dropdown box on the "open action
item" worksheet under the 'status' column. If it is filtered, then he won't
see open, in-progress, etc.. on the hard copy he hands out, without multiple
printouts. The whole process enables the PL to quickly review what closed
the previous day and then move on to all other items. After our meeting he
will select the 'Move Closed Action Items' to move it to the "Closed action
items" worksheet.

It is long, but I hope this helps.
Thanks!
jfree223

"Mike H." wrote:

Now I am even more confused. You have an Open Action Items List. And you
want to put closed items on this "Open" list? Your terms either don't match
what is actually on the list or I am not getting what is going on. Can you
try to explain your scenario one more time perhaps.

"JFREE223" wrote:

Great Idea and I have that established. Before it is moved, some individuals
would like to see it on the one sheet we print out...which is the open action
items list. For ease of review, they would like to see them at the top and
then move them to the closed list after it is reviewed. I have filters
established, but again they need to see all action items on the printout.

Thanks,
jf223

"Mike H." wrote:

What if you have more than 8 items with, say, Open status. You'll be moving
a closed item into the middle of your open stuff? Doesn't make sense to me
that you would want to do this. Why not move the closed items to a different
sheet?

"JFREE223" wrote:

I have an action item spreadsheet I put together which contains a status in
column C. The status is Open, Closed, In-Progress, On-Hold, On-going (these
are part of my validation list). If the action item in the cell is "Closed"
I would like to move the entire row to row 9, without overriding what is
there. So whatever was in row 9 would be in row 10 to make room for the
closed action item. To make things more difficult, I have conditional
formatting to grey out ever other row for reading ease. Any help would be
appreciated.

Thanks!