Cutting and Pasting info based on specific values

clara200

New Member
Joined
Jul 19, 2006
Messages
28
Hey Everyone,

I am having a little trouble with one of my macros in excel. I am trying to apply the code I was using for a different worksheet to another worksheet. The problem I have is in this worksheet the information I am filtering for is different. It filters for the info for CRRET and 03/J transactions fine. The problem is there is also info in coloumn J that has only one unique property about it. Some of the transactions begin with the letter D ans then a number eg D123456. The other transactions are like CRRET1234 and 03/J123 so it filters them fine, but it won't filter any of the transactions that begins with D.

Is there a way of filtering for say "Dnumber" i would say that would do the trick.

Code:
Sub Macro9()
    Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J1 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 5)
        Case "CRRET"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J1"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

Thanking you in advance,
Seán.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
thanks Andrew for your reply. that worked like a dream. it was so simple and I couldn't even think of it.

any chance you know or anyone for that matter how you can manipulate the code to include more that just three headings, and extracting just the criteria of three items. Eg CRRET, D0000, 03/J4, CQIND etc.

The code was given to me by "onlyadrafter" and I have been trying to include a fourth item but have been failing miserably
 
Upvote 0
Code:
Sub ABC()
Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CQ Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D000000 To D999999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J0 To 03/J4 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 5)
        Case "CQ"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D000000" To "D999999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "CRRET"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J0" To "03/J4"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

When I enter this it my sheet goes it sorts everthing incorrectly and I have discovered that not only does it sort the transactions incorrectly it deletes some as well

Seán
 
Upvote 0
What's?

Case "CQ"
Case "D000000" To "D999999"

They are 2 and 7 characters respectively, not the 5 extracted by:

Select Case Left(Range("J" & MY_ROWS).Value, 5)
 
Upvote 0
Code:
Sub ABC()
Application.ScreenUpdating = False
    Sheets("RET").Select
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CQRET000 To CQRET999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of D000000 To D999999 Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of CRRET Transactions"
    Range("A65536").End(xlUp).Offset(3, 0).Value = "Group of 03/J0 To 03/J4 Transactions"
   
    For MY_ROWS = Range("J65536").End(xlUp).Row To 1 Step -1
    Select Case Left(Range("J" & MY_ROWS).Value, 8)
        Case "CQRET000" To "CQRET999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "D000000" To "D999999"
            Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "CRRET0000" To "CRRET9999"
            Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).End(xlUp).Offset(1, 0)
            Rows(MY_ROWS).Delete
        Case "03/J0" To "03/J4"
            Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Insert
            Rows(MY_ROWS).Cut Destination:= _
                Range("A65536").End(xlUp).Offset(1, 0)
             Rows(MY_ROWS).Delete
        End Select
    Next MY_ROWS
End Sub

Right the code is working fine now except for one small little thing intstead of the CQRET transactions coming in under their own heading. They are comin in under the D00000 heading. apart from that everyting to my eyes looks ok
 
Upvote 0
when you say add another End(xlUp) Statement where do you mean.

For CQRET there is already four and only three for D000

Case "CQRET000" To "CQRET999"
Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
Rows(MY_ROWS).Cut Destination:= _
Range("A65536").End(xlUp).End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
Rows(MY_ROWS).Delete


Case "D000000" To "D999999"
Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).EntireRow.Insert
Rows(MY_ROWS).Cut Destination:= _
Range("A65536").End(xlUp).End(xlUp).End(xlUp).Offset(1, 0)
Rows(MY_ROWS).Delete
 
Upvote 0
Sorry, I'm sure there were only 3 when I read it.

When only the headings are present End(xlUp) 4 times will take you to the first header. But when data is added below the header(s) it won't, unless the source data is sorted in reverse order to the order of the headings.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top