Moving 'Closed' Rows
 

Hi,

I run a risk register on an excel worksheet. A risk status colum
indicates whether the risk is either proposed, open or closed.

I have tried to make a macro (using the record macro option) to mov
closed risks to another sheet imaginatively titled 'Closed Risks'.
have found various issues using cutting, and ensuring only visibl
cells are copied so perhaps the method seems rather longwinded:

First it unhides columns as necessary, runs an autofilter on the ris
status column. selects visible cells, copies them, inserts the
somewhere else then goes back to delete the originals (cut seemed no
to work at all). The problem is that the although it seems to selec
the right cells it does not actually insert them at all (although i
does delete them). Either I am missing something obvious (possible) o
perhaps i need a more streamlined way of performing this action.

The area i am concerned about is in bold below, any alternative idea
very gladly recieved!

thx in advance

Nejl

Sub TRANSPOSE()
'
' TRANSPOSE Macro
' Macro recorded 05/10/2004 by Nathan
'

'
Sheets("Closed Risks").Select
Application.CutCopyMode = False
ActiveWorkbook.CustomViews("Closed Risks Forms").Show
Range("A5").Select
Sheets("Risk Register").Select
ActiveWindow.ScrollColumn = 57
Sheets("Risk Register").Select
ActiveWorkbook.CustomViews("Formulae").Show
ActiveWindow.ScrollColumn = 53
Columns("BS:BS").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Closed"
ActiveWindow.ScrollColumn = 1
*Rows("5:150").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Closed Risks").Select
Selection.Insert Shift:=Range("A65536").End(xlUp)(2).Select
Range("B5:B6").Select *
ActiveWorkbook.CustomViews("Closed Risks Full").Show
Sheets("Risk Register").Select
ActiveWorkbook.CustomViews("Formulae").Show
ActiveWindow.ScrollColumn = 53
Range("BS2:BS3").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 53
Selection.AutoFilter
Columns("BS:BS").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Closed"
ActiveWindow.ScrollColumn = 1
Rows("5:151").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.CustomViews("Full").Show
ActiveWindow.ScrollColumn = 29
End Su

--
nejlangto
-----------------------------------------------------------------------
nejlangton's Profile: http://www.excelforum.com/member.php...fo&userid=1397
View this thread: http://www.excelforum.com/showthread.php?threadid=26649