Macro/ VBA - Move rows to new worksheet

[night of the seven ifs]

Hi there, I was wondering if anyone out there might be able to help me, I am looking for a way to take all rows from a spreadsheet where the value in column U is 'closed' and transfer them automatically to a different worksheet. I discovered the world of Macro and VBA today and have been quite enjoying it actually, but unfortunately despite my best attempts at adapting similar macros, I have not yet been able to produce any results. Could anyone help me or provide any advice with this? Or perhaps advise on how I can adapt a similar macro?

Many thanks in advance techsperts

 

[JackDanIce]

Welcome to the board.

Assuming your first row is your header row (starting in cell[A1]) and the data is in the first spreadsheet and by transfer you mean cut and paste try:

Sub DeleteClosedInColumnU ()
With Sheets("Sheet1")
    If .AutoFilterMode Then Cells.AutoFilter
    .Range("A1").CurrentRegion.AutoFilter field:=21, Criteria1:="Closed"
    .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).EntireRow.Cut Sheets("Sheet2").Range("A1") 
    .Range("A1").CurrentRegion.AutoFilter
End With
End Sub

Warning not tested, try on some dummy data

 

[night of the seven ifs]

Hey JackDanIce, thanks for the response, just gave it a go and I got the message-

"Run time error 9

Subscript out of range"

Any ideas?

Thanks again for responding

 

[night of the seven ifs]

Don't know if this is helpful, but this is another VBA script that was suggested (and apparently worked) for another user on the forum. I did try altering it myself but it seems my knowledge does not yet extend far enough for this! Here's the code-

Sub Extract_Data()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'this macro assumes that your first row of data is a header row.<o></o>
'will copy a row from one worksheet, to another blank workbook<o></o>
'IF there is a “closed” in column U<o></o>
'Variables used by the macro<o></o>
Application.ScreenUpdating = False<o></o>
Dim FilterCriteria<o></o>
Dim CurrentFileName As String<o></o>
Dim NewFileName As String<o></o>
<o> </o>
Set a = ActiveSheet<o></o>
'<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Select</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace><o></o>
'(note you can change this to meet your requirements)<o></o>
Range("A1:AS4000").Select<o></o>
'Apply Autofilter<o></o>
Selection.AutoFilter<o></o>
FilterCriteria = closed<o></o>
'NOTE - this filter is on column U (field:=21), to change<o></o>
'to a different column you need to change the field number<o></o>
Selection.AutoFilter field:=21, Criteria1:=FilterCriteria<o></o>
'Select the visible cells (the filtered data)<o></o>
Selection.SpecialCells(xlCellTypeVisible).Select<o></o>
'Copy the cells<o></o>
Selection.Copy<o></o>
'Open a new file<o></o>
'Workbooks.Add Template:="Workbook"<o></o>
'Get this file's name<o></o>
'NewFileName = ActiveWorkbook.Name<o></o>
Sheets.Add().Name = "closed"<o></o>
Set b = ActiveSheet<o></o>
'Make sure you are in cell A1<o></o>
Range("A1").Select<o></o>
'Paste the copied cells<o></o>
ActiveSheet.Paste<o></o>
Range("A1").Select 'unselect everything<o></o>
'Clear the clipboard contents<o></o>
Application.CutCopyMode = False<o></o>
'Go back to the original file<o></o>
'Workbooks(CurrentFileName).Activate<o></o>
a.Select<o></o>
Selection.AutoFilter field:=21, Criteria1:=FilterCriteria<o></o>
Selection.SpecialCells(xlCellTypeVisible).Select<o></o>
'Clear the autofilter<o></o>
'Selection.AutoFilter field:=1<o></o>
'Take the Autofilter off<o></o>
Selection.AutoFilter<o></o>
'Go to A1<o></o>
'Range("A1").Select<o></o>
Application.ScreenUpdating = True<o></o>
End<o></o>
End Sub

 

[JackDanIce]

What is the worksheet your data is in and what is the entire range your data is in?

 

[night of the seven ifs]

Hi ok it's a worksheet with a list of suggested improvements. In the u column there is formatting to mark each cell as either open, overdue, or closed. I've been asked to set something up so that when a 'case' has been closed, the whole row automatically jumps to a second worksheet- so there ends up being a new worksheet for closed cases. The current range of the sheet is a-y and 1-150. Is this what you meant? Hope I haven't misunderstood.

 

[JackDanIce]

Ok this code will move your data from the first sheet to the second sheet in the same workbook - I'm having to guess where the source data is and where you want it moved to:

Sub DeleteClosedInColumnU ()
Dim i as Long
i = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
With Sheets(1)
    If .AutoFilterMode Then Cells.AutoFilter
    .Range("A1:Y" & i).AutoFilter field:=21, Criteria1:="Closed"
    .Range("A1:Y" & i).SpecialCells(xlCellTypeVisible).EntireRow.Cut Sheets(2).Range("A1") 
    .Range("A1").CurrentRegion.AutoFilter
End With
End Sub

 

[night of the seven ifs]

Hi ok I just ran this and got another message-


Run time error '1004'
The command could not be completed using the range specified. Select a single cell within the range and try the command again.


Hmm. Pressing debug the 6th line has been highlighted. What information do you need about the source data and target location?

 

[night of the seven ifs]

Oops please excuse me I was trying it on an old version of the file to be safe, and the columns are in a different order. Will run again on an updated version. Thanks

 

[night of the seven ifs]

Ok tried that on the correct worksheet and recieved same message