Solved

Filter, cut and paste data in new worksheet

Posted on 2013-02-06
20
337 Views
Last Modified: 2013-02-13
Hi,

I have a filter on row8 across all fields in sheet "Pending Ports".  cell T20 is filtered on values"Rejected"and "Cancelled"

these values should be cut ans pasted in sheet 'RejectedCancelled" starting from row A10 and thereafter all cut and paste values / data will be pasted on the first availble blank cell.

any help is urgently appreciated

thanks
0
Comment
Question by:shamilaz
  • 9
  • 7
  • 4
20 Comments
 
LVL 16

Expert Comment

by:terencino
Comment Utility
Hi shamilaz, give this macro a try. It uses a search>cut>paste> delete approach, doesn't use AutoFilter functionality at all. The Advanced Filter is only suitable for the same sheet, so I thought this would be easier and quicker to get a result for you.
Let me know how it goes
...Terry
Sub MoveRejects()
Dim c As Range, port_sheet As Worksheet, reject_sheet As Worksheet
Set port_sheet = Worksheets("Pending Ports")
Set reject_sheet = Worksheets("RejectedCancelled")
reject_column = 20
Application.ScreenUpdating = False
Do Until finished = "Yes"
For Each c In port_sheet.Columns(reject_column).Cells
If c.Row > port_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row Then
    finished = "Yes"
    Exit For
End If
    If c.Value = "Rejected" Or c.Value = "Cancelled" Then
        port_sheet.Rows(c.Row).Copy reject_sheet.Rows(reject_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
        port_sheet.Rows(c.Row).Delete
    End If
Next c
Loop
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:terencino
Comment Utility
Oh, here's the spreadsheet I used for the trial...
MoveRejected.xlsm
0
 

Author Comment

by:shamilaz
Comment Utility
Clarification:-)

I tested it on my WS.  It removes cancelled and rejected in stages.  example I have to run the code a few times for it to take out record by record ...Is that how its supposed to be and not take all rejected and cancelled in one click or run of the macro?

also would I forgot to mention...would it be possible to do a macro to undo what has been moved if thats ok with you...my apologies...I can ask as another question if need be.

thanks
0
 
LVL 16

Expert Comment

by:terencino
Comment Utility
No it was supposed to remove them in one go. Hard to tell why without your spreadsheet. I'll try a different approach and get back to you soon
0
 
LVL 10

Expert Comment

by:broro183
Comment Utility
hi everyone,

Terry, when deleting rows the code needs to work from the bottom up, otherwise each row deletion causes  the "next" row becomes the "current" row before the row is incremented at the end of the loop. In a loop this can be done using a syntax along the lines of "for i = LastRow to HdrRow Step -1...".

Shamilaz,
Here's a version that uses autofilter. It may need some adjustments...

Option Explicit

Sub Macro5()
Const FirstPasteRow As Long = 10
Dim PasteWs As Worksheet
Dim AfRng As Range
Dim AfDataOnlyRng As Range
Dim PasteCll As Range
Dim VisClls As Range

    With ThisWorkbook
        Set AfRng = .Worksheets("Pending Ports").AutoFilter.Range
        Set PasteWs = .Worksheets("RejectedCancelled")
    End With

    With PasteWs
        Set PasteCll = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
        If PasteCll.Row < FirstPasteRow Then
            Set PasteCll = .Range("A" & FirstPasteRow)
        End If
    End With

    With AfRng
        Set AfDataOnlyRng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
        '###note this doesn't check for any other filters (before applying the below filtering criteria), do you need it to?
        .AutoFilter Field:=20, Criteria1:= _
                    "=Cancelled", Operator:=xlOr, Criteria2:="=Rejected"
        Set VisClls = AfDataOnlyRng.SpecialCells(xlCellTypeVisible)
        If Not VisClls Is Nothing Then
            VisClls.Copy PasteCll
        End If
        .AutoFilter Field:=20
    End With

    Set VisClls = Nothing
    Set PasteCll = Nothing
    Set PasteWs = Nothing
    Set AfRng = Nothing
    Set AfDataOnlyRng = Nothing
End Sub

Open in new window


hth
Rob
0
 

Author Comment

by:shamilaz
Comment Utility
Hi  terry,

the other issue that I am facing with the code that you sent me is ...the values that are copied(that are in "Pending Ports") are getting some rows from a vlookup.  so when the records are copied and moved to "RejectedCancelled"  the vlookup values just show"VAlue" and nothing else or the wrong street address.  any ideas where when pasting it can be done as "values" so that it does not look for a lookup value?

ss attached,

thanks

Rob,  thanks for yr submission as well......ran into an error 400
0
 

Author Comment

by:shamilaz
Comment Utility
0
 
LVL 16

Expert Comment

by:terencino
Comment Utility
OK Shamilaz, here is an updated version using the AutoFilter as recommended by Rob. It is  a bit clumsy using copy & paste special values but it does work. Can you try this and let me know. It should be relatively easy to reverse the process to "undo" the change, but likely all the rows that were moved would be added back at the end of the first list

Sub MoveRejects()
Dim c As Range, port_sheet As Worksheet, reject_sheet As Worksheet, r As Range
Set port_sheet = Worksheets("Pending Ports")
Set reject_sheet = Worksheets("RejectedCancelled")
reject_column = 19
Application.ScreenUpdating = False
port_last_row = port_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
port_sheet.ListObjects("Table2").Range.AutoFilter Field:=reject_column, Criteria1:= _
    "=Cancelled", Operator:=xlOr, Criteria2:="=Rejected"
For Each r In port_sheet.ListObjects("Table2").Range.Rows
i = i + 1
    If i = 1 Then
        GoTo skip
        End If
    If r.Hidden = True Then GoTo skip
    reject_last_row = reject_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    r.Copy
    reject_sheet.Range("A" & reject_last_row).PasteSpecial xlPasteValues
    reject_sheet.Range("A" & reject_last_row).PasteSpecial xlPasteFormats
skip:
Next r
Application.CutCopyMode = False
port_sheet.Rows("9:" & port_last_row).delete Shift:=xlUp
port_sheet.ShowAllData
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:shamilaz
Comment Utility
Thanks Terry, works great just as you stated.....in case I want to reverse the cut and paste how would I undo the change?...is there a simple change

thanks
0
 
LVL 16

Expert Comment

by:terencino
Comment Utility
I think we could store the rows they were moved from and to, either in a global variable or even on a hidden sheet. So that way we would be able to move thing back to where they cam from. The formulas would need to be reinstated, and there might be some issue with the defined tables (they are ListObjects not just plain ranges) but it could be done without too much difficulty I think. I'll have a look at it tomorrow!
...Terry
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:shamilaz
Comment Utility
thanks Terry...much appreciated
0
 

Author Comment

by:shamilaz
Comment Utility
Also Terry, the same file but fewer columns, I tried amending and applying the same formula/macro that worked earlier and it failed.  Appreciate if you could have a look and amend as a whole for the attached doc please...and then we are good to go.  When the Status (Column 9 changes from OPEN to CLOSED, these records should move to the incidents closed tab
TEMP.xlsx
0
 
LVL 16

Expert Comment

by:terencino
Comment Utility
This works for the new workbook, mostly the same structure as the original, different in a many other respects as it is very specific to the structure of the tables and names. You could look at the code side by side to see the differences, let me know if you need any further explanation. Note that now it stores the rows moved from and to, and also included is another macro as discussed to "undo" the previously moved rows MoveClosedUndo.
...Terry


Public MovedFromRows, MovedToRows

Sub MoveClosed()
Dim c As Range, open_sheet As Worksheet, closed_sheet As Worksheet, r As Range
Set open_sheet = Worksheets("Incidents OPEN")
Set closed_sheet = Worksheets("Incidents CLOSED")
closed_column = 9
Application.ScreenUpdating = False
open_last_row = open_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
open_sheet.ListObjects("Table6").Range.AutoFilter Field:=closed_column, Criteria1:="=Closed"
For Each r In open_sheet.ListObjects("Table6").Range.Rows
    i = i + 1
    If i = 1 Then
        GoTo skip
        End If
    If r.Hidden = True Then GoTo skip
    closed_last_row = closed_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    r.Copy
    closed_sheet.Range("A" & closed_last_row).PasteSpecial xlPasteValues
    closed_sheet.Range("A" & closed_last_row).PasteSpecial xlPasteFormats
    j = j + 1
    If j = 1 Then
        MovedFromRows = r.Row
        MovedToRows = closed_last_row
    Else
        MovedFromRows = MovedFromRows & "|" & r.Row
        MovedToRows = MovedToRows & "|" & closed_last_row
    End If
skip:
Next r
Application.CutCopyMode = False
open_sheet.Rows("9:" & open_last_row).Delete Shift:=xlUp
Range("Table6[[#Headers],[Status]]").Select
open_sheet.ShowAllData
Application.ScreenUpdating = True
End Sub

Sub MoveClosedUndo()
Dim c As Range, open_sheet As Worksheet, closed_sheet As Worksheet, r As Range, a As Variant, newRow As ListRow
Set open_sheet = Worksheets("Incidents OPEN")
Set closed_sheet = Worksheets("Incidents CLOSED")
Application.ScreenUpdating = False
a = Split(MovedToRows, "|")
For k = LBound(a) To UBound(a)
    Set newRow = open_sheet.ListObjects("Table6").ListRows.Add
    closed_sheet.Rows(CLng(a(k))).Copy
    newRow.Range.Cells(1, 1).PasteSpecial xlPasteValues
    newRow.Range.Cells(1, 1).PasteSpecial xlPasteFormats
Next k
Application.CutCopyMode = False
closed_sheet.Rows(a(LBound(a)) & ":" & a(UBound(a))).Delete Shift:=xlUp
Application.ScreenUpdating = True

End Sub

Open in new window

TEMP.xlsm
0
 

Author Comment

by:shamilaz
Comment Utility
Hi Terry,

Tried using it on this version.  Should have worked but does not for the "Incidents OPEN " and "Incidents CLOSED " tabs....kindly appreciate if you could please have a look and get back.

and if required amend the code to this sample...I think the different versions may be the issue

thanks very much
original-sample.xlsm
0
 

Author Comment

by:shamilaz
Comment Utility
Hi Terry,

Instead of the last macro you sent me I used the following and it cuts / deletes the "closed" record nicely, but does not put it in the next blank row in the Incidents CLOSED sheet.  It instead does it over and over again in the same row.  Appreciate greatly if you can amend this macro at least Terry, Thanks very much

Sub MoveEm()
    Dim rng1 As Range, rng2 As Range, cel As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchStr As String
    Dim i As Long
 
    Set ws1 = Sheets("Incidents OPEN")
    Set ws2 = Sheets("Incidents CLOSED")
    SearchStr = "Closed"
 
    Set rng1 = Intersect(ws1.UsedRange, ws1.Columns("G"))
    If rng1 Is Nothing Then Exit Sub
 
    Application.ScreenUpdating = False
 
    Do
        Set cel = rng1.Find(SearchStr, , xlValues, xlWhole, xlByRows)
        If cel Is Nothing Then Exit Do
        i = i + 1
        ws2.Rows(i).Value = ws1.Rows(cel.Row).Value
        ws1.Rows(cel.Row).copy
        ws2.Rows(i).PasteSpecial Paste:=xlPasteFormats
        ws1.Rows(cel.Row).EntireRow.delete
        Set cel = Nothing
    Loop
   
    Application.ScreenUpdating = True
 
End Sub
0
 
LVL 16

Accepted Solution

by:
terencino earned 350 total points
Comment Utility
Hi shamilaz, that does work well, here are the changes I made (only minor), also listed in attached file. Probably time to close this question off though!
...Terry
Sub MoveEm()
    Dim rng1 As Range, rng2 As Range, cel As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim SearchStr As String
    Dim i As Long
 
    Set ws1 = Sheets("Incidents OPEN")
    Set ws2 = Sheets("Incidents CLOSED")
    SearchStr = "Closed"
    ' Changed Columns("G") to Columns("I")
    Set rng1 = Intersect(ws1.UsedRange, ws1.Columns("I"))
    If rng1 Is Nothing Then Exit Sub
 
    Application.ScreenUpdating = False
    'Added this to find last row in ws2
    closed_last_row = ws2.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    Do
        Set cel = rng1.Find(SearchStr, , xlValues, xlWhole, xlByRows)
        If cel Is Nothing Then Exit Do
        i = i + 1
        'incremented last row by 1
        ws2.Rows(closed_last_row + i).Value = ws1.Rows(cel.Row).Value
        ws1.Rows(cel.Row).Copy
        'incremented last row by 1
        ws2.Rows(closed_last_row + i).PasteSpecial Paste:=xlPasteFormats
        ws1.Rows(cel.Row).EntireRow.Delete
        Set cel = Nothing
    Loop
    
    Application.ScreenUpdating = True
  
End Sub

Open in new window

TEMP.xlsm
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 150 total points
Comment Utility
hi,

I'm sorry I haven't been back sooner. The below code probably doesn't meet the latest requirements but for giggles I thought I would post what I'd done (macro6).

Terry,
re this post: Public variables can be unintentionally cleared when if excel's state is reset (eg a macro stops or...?). To overcome this I'd use either use 1) a hidden Defined Name (but I think there is a limit to the string length - possibly 255 characters?) or 2) cells on a hidden sheet which are referred to by a Named Range.

Shamilaz,
re your last post:
1) Does it make an difference if you add an "after" parameter into the .Find section, for example:
''Help Files give the syntax as:
'expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
''does it help if you change the code from...
'Set cel = rng1.Find(SearchStr, , xlValues, xlWhole, xlByRows)
''to:
Set cel = rng1.Find(SearchStr, rng1.Cells(1, 1), xlValues, xlWhole, xlByRows)
''or:
Set cel = rng1.Find(SearchStr, rng1.Resize(1), xlValues, xlWhole, xlByRows)

Open in new window

2) I think that the "i" variable needs to be pre-populated before the code gets into the loop. Does this modification help?

Set ws2 = Sheets("Incidents CLOSED")
'add this row to identify the last populated row.
i = Application.WorksheetFunction.Max(ws2.Range("A" & .Rows.Count).End(xlUp).Row, FirstPasteRow)
SearchStr = "Closed"

Open in new window



Option Explicit

Sub Macro6()
Const FirstPasteRow As Long = 10
Const ColToFilter As Long = 19    'concept borrowed from Terry's code!

Dim port_sheet As Worksheet
Dim PasteWs As Worksheet
Dim AfRng As Range
Dim AfDataOnlyRng As Range
Dim PasteCll As Range
Dim VisClls As Range

    '    With ThisWorkbook
    With ActiveWorkbook
        Set port_sheet = .Worksheets("Pending Ports")
        Set PasteWs = .Worksheets("RejectedCancelled")
    End With

    With port_sheet.ListObjects("Table2")
        Set AfRng = .Range
        Set AfDataOnlyRng = .DataBodyRange
    End With

    With PasteWs
        Set PasteCll = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
        If PasteCll.Row < FirstPasteRow Then
            Set PasteCll = .Range("A" & FirstPasteRow)
        End If
    End With

    With AfRng
        '###note this doesn't check for any other filters (before applying the below filtering criteria), do you need it to?
        .AutoFilter Field:=ColToFilter, Criteria1:= _
                    "=Cancelled", Operator:=xlOr, Criteria2:="=Rejected"

        On Error Resume Next
        Set VisClls = AfDataOnlyRng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        If Not VisClls Is Nothing Then
            With VisClls
                .Copy PasteCll
                'Turning off the alerts means that the entire rows will be deleted without warning.
                'There is probably a better way doing this, but I can't get "viscells..entirerows.delete" to work.
                Application.DisplayAlerts = False
                .Delete
                Application.DisplayAlerts = True
            End With
        End If
        .AutoFilter Field:=ColToFilter
    End With

    Set VisClls = Nothing
    Set PasteCll = Nothing
    Set PasteWs = Nothing
    Set AfRng = Nothing
    Set AfDataOnlyRng = Nothing
    Set port_sheet = Nothing
End Sub

Open in new window


hth
Rob
0
 
LVL 10

Expert Comment

by:broro183
Comment Utility
Ooopps, I was too slow again!

At least my thoughts are along the same lines as Terry's ie he uses "closed_last_row + i".

Rob
0
 

Author Closing Comment

by:shamilaz
Comment Utility
Hi Terry and Rob,

You guys were both great:-).  However Rob I hope you understand that I had to allocate more points for Terry due to his time on these requests and Terry I do hope you understand that Rob contributed as well.

Both you gentlemen have been extremely helpful and I will surely be reaching out to you both again:-)

Thanks very much

Shamil
0
 
LVL 10

Expert Comment

by:broro183
Comment Utility
hi Shamil,

Thank you for the points, I definitely understand, in fact I'm surprised to get any points. Please do feel free to give them all to Terry.

Would you mind posting your final solution?
This may be helpful for other users who search the EE site.

Rob
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now