Macro's : Cut/Paste from One Sheet to Another

[wtknsphil]

Hi all,

Long-time lurker now registered. Looking for a bit of help.

I did a search on here for taking a row, for example from "Sheet1" and moving it to "Sheet2". The code works in principle however on occasion it takes two rows across. The key word is "YES" in column 'i' and the macro is enabled with a button.

Could anyone advise why it sometimes takes two rows, even though I'm only putting "yes" in one row?

Thanks in advance

Sub Button1_Click()
     
    Application.ScreenUpdating = False
    Columns(9).AutoFilter 1, "Yes"
    With Range("a2", Range("i" & Rows.Count).End(3))
        .Copy Sheet2.Cells(Rows.Count, 1).End(3).Offset(1)
        .EntireRow.Delete
    End With
    Columns(9).AutoFilter
    Application.ScreenUpdating = True
    Selection.AutoFilter
     
End Sub

 

[wtknsphil]

Update: Just been playing with it to try and fault find.

When I type 'yes' in column 'i' for two different rows, it will take one row and leave another. When I don't type yes in any of the rows and press the button, it will take row A1 regardless.

 

[vcoolio]

Hello Wtknsphil,

I'm not too sure why that is happening but try removing the second last line of code:-

Selection.Autofilter

Just in case that doesn't solve it, try the following amended code:-

Sub Button1_Click()
     
Application.ScreenUpdating = False
    
    With Sheet1.[A1].CurrentRegion
        .AutoFilter 9, "Yes"
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    
Application.CutCopyMode = False
Application.ScreenUpdating = True
    
End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[JackDanIce]

Maybe copy visible rows only? Try:

Sub Button1_Click()
     
    Dim x   As Long
     
    Application.ScreenUpdating = False
    
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.count, 9).End(xlUp).row
        With .Cells(1, 1).Resize(x, 9)
            .AutoFilter
            .AutoFilter Field:=9, Criteria1:="Yes"
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
                Sheet2.Cells(Rows.count, 1).End(xlUp).Offset(1)
        End With
        .AutoFilterMode = False
    End With
        
    Application.ScreenUpdating = True
     
End Sub

 

[wtknsphil]

Thanks for the replies, both.

vcoolio - The amendment to the code didn't seem to work and the code that you attached wouldn't work either.

JackDanIce - Couldn't get the row to move at all with that code

I have managed to get a row to transfer across, but it now replaces row '2' on Sheet2 every time, rather than add the cut line underneath it.

 

[JackDanIce]

Just tested it on a dummy workbook, it filters data in columns A:I, where values in column I are Yes and copies those visible rows to Sheet2, then deletes those rows from the source sheet:

Sub Button1_Click()
     
    Dim x   As Long
     
    Application.ScreenUpdating = False
    
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.count, 9).End(xlUp).row
        With .Cells(1, 1).Resize(x, 9)
            .AutoFilter
            .AutoFilter Field:=9, Criteria1:="Yes"
            With .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible)
                .Copy Sheet2.Cells(Rows.count, 1).End(xlUp).Offset(1)
                .Delete shift:=xlUp
            End With
       End With
        .AutoFilterMode = False
    End With
        
    Application.ScreenUpdating = True
     
End Sub

I do not understand what you mean by it did not move I'm afraid.

 

[vcoolio]

Same here. I've just tested my code in a mock-up of what I believe your workbook to be like (based on your opening post) and it works just fine. I also tested JackDanIce's code in my mock-up and it too works just fine.

Something else may be at play here so it would be best if you could upload a sample of your workbook to a free file sharing site (such as DropBox) and then post the link to your file back here. Please use dummy data but ensure that the set out of the sample is exactly the same as your actual workbook. We should then be able to tell what is causing the problem.

Cheerio,
vcoolio

 

[wtknsphil]

I think you may be on to something with the workbook. When I test on a new 'blank' workbook it seems to work fine, when I try and put it in the workbook that I am modifying, it falls over.

Will have a look and see about uploading.

Thanks both for your help so far - appreciated

 

[vcoolio]

One of the regular "suspects" is merged cells. But anyway, upload a sample and we'll take a look.

Cheerio,
vcoolio.

 

[wtknsphil]

Hi guys,

Thought id post back with the resolution. It was very simple - Sheet2 was one column out (A was a blank column rather than starting there). Deleted this and the code worked fine. Also put a macro in that a colleague helped with which prevents the macro from being run if the trigger cell is blank.

Thanks again for the help!