Move and delete rows

[Ross86]

Hi,

I am trying to create a macro the looks at column A and moves the entire row to another worksheet or deletes the entire row based off of the value in each cell. Right now I have the Macro replace the downloaded values to either AG, G, GI, ICP, IMG or delete. The idea be to move all of the values with AG in column A to the AG worksheet, move G to the G worksheet, etc.. and then delete all of those with delete in the cell. This is what I have so far, could someone help me to create this???

Sub Reformat_macro()
'
' Reformat_macro Macro
' Macro recorded 9/21/2009 by rcoates
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("C:C,D:D,I:I,J:J").Select
    Range("J1").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Range("C:C,D:D,I:I,J:J,K:K,L:L,M:M,N:N").Select
    Range("N1").Activate
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    Selection.Replace What:="9999428", Replacement:="cash_usd", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="3390-2261", Replacement:="ICP", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="3243-9703", Replacement:="Delete", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="1208-2340", Replacement:="IMG", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="1623-2668", Replacement:="Delete", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="7461-9011", Replacement:="Delete", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="5838-6867", Replacement:="Delete", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="7103-2902", Replacement:="Delete", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="7122-7716", Replacement:="GI", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="4125-7242", Replacement:="G", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="4810-6156", Replacement:="AG", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

[Ross86]

Added Code tags

[shg]

CODE tags, please, not PHP tags.

Thanks.

[Ross86]

Sorry, code tags added

[shg]

I think this does the same as your code (which you could verify).

Sub Reformat_macro()
    ' Keyboard Shortcut: Ctrl+Shift+M

    Columns("A").Delete
    Rows(1).Delete
    Columns("B").Delete
    Range("C:C,D:D,I:I,J:J,K:K,L:L,M:M,N:N").Delete

    Cells.Replace What:="9999428", Replacement:="cash_usd", _
                  LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="3390-2261", Replacement:="ICP"
    Cells.Replace What:="1208-2340", Replacement:="IMG"
    Cells.Replace What:="7122-7716", Replacement:="GI"
    Cells.Replace What:="4125-7242", Replacement:="G"
    Cells.Replace What:="4810-6156", Replacement:="AG"
    
    Cells.Replace What:="3243-9703", Replacement:="Delete"
    Cells.Replace What:="1623-2668", Replacement:="Delete"
    Cells.Replace What:="7461-9011", Replacement:="Delete"
    Cells.Replace What:="5838-6867", Replacement:="Delete"
    Cells.Replace What:="7103-2902", Replacement:="Delete"
End Sub

Now you want to move the cells containing "GI" to sheet GI? Just individual cells or entire rows? Move them to the bottom of exsiting data?

[Ross86]

Thanks, that's great. Yeah I want to move all the rows with GI in column A to sheet GI, then move all the rows with AG to sheet AG, etc. There is no existing data on those sheets.

[shg]

Will that be the only text in those cells?

[Ross86]

Text in column A, Date in Column B, Text in column C, number value in column D

[Ross86]

But yes, in Column A it will only be either AG, G, GI, IMG, ICP or delete.. nothing else in the column

[shg]

Try this (completely untested)O. Post a workbook if it doesn't work.

Sub Reformat_macro()
    ' Keyboard Shortcut: Ctrl+Shift+M

    Dim v As Variant
    Dim wks As Worksheet
    
    Columns("A").Delete
    Rows(1).Delete
    Columns("B").Delete
    Range("C:C,D:D,I:I,J:J,K:K,L:L,M:M,N:N").Delete

    Cells.Replace what:="3243-9703", Replacement:="Delete", _
                  LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace what:="1623-2668", Replacement:="Delete"
    Cells.Replace what:="7461-9011", Replacement:="Delete"
    Cells.Replace what:="5838-6867", Replacement:="Delete"
    Cells.Replace what:="7103-2902", Replacement:="Delete"
    
    Cells.Replace what:="9999428", Replacement:="cash_usd"
    Cells.Replace what:="3390-2261", Replacement:="ICP"
    Cells.Replace what:="1208-2340", Replacement:="IMG"
    Cells.Replace what:="7122-7716", Replacement:="GI"
    Cells.Replace what:="4125-7242", Replacement:="G"
    Cells.Replace what:="4810-6156", Replacement:="AG"
    
    For Each v In Array("ICP", "IMG", "GI", "G", "AG")
        ActiveSheet.AutoFilterMode = False
        Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=v
        ActiveSheet.AutoFilter.Range.Cut Destination:=Worksheets(v).Range("A1")
    Next v

    ActiveSheet.AutoFilterMode = False
End Sub

[Ross86]

It doesn't seem to be working. Would it also be possible to add a column with todays date as the last column? I have attached a test file for you.

[shg]

See attached.

[Ross86]

That is Great!! Thank you so much. There is one more thing that would complete this process for me. Is it possible once all of the rows are places on there respective sheets to then go and replace the AG with todays date??

By that I mean the Sheet names will stay the same, however, column A will display the date in MM/DD/YYYY format. Is this possible?