Move rows to new sheet on condition

**mjsmith127** · 04-20-2010, 12:48 PM

i'm new to this and i am hoping someone can help.

im trying to re-vamp database managment procedures at work and have got stuck trying to use some functions of visual basic.

i am trying to move an entire row from a database on 'sheet 1' to the first empty row on 'sheet 2' dependant on column 'B' of 'sheet 1' containing an 'N'.

this may sound simple enough, but i have spent the last day and a half researching on the net, ironically greatly reducing my current poductivity, and getting nowhere.

macros thati have tried will move a row but each subsequent row will overwrite each previously moved row. agghhh.

any help will be greatly appreciated.

**Palmetto** · 04-20-2010, 01:09 PM

Given the lack of details and a sample workbook, perhaps this can be a start for you.

Option Explicit

Sub MoveRows()

    Dim lastrow As Long
    
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    Application.ScreenUpdating = False
    
    With Sheet1
    
        .Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="=N", Operator:=xlAnd
        .Range("a1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A" & lastrow)
        .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

See attached.

**mjsmith127** · 04-20-2010, 01:14 PM

thanks for the input will give it a shot.

**mjsmith127** · 04-21-2010, 05:12 AM

have just tried it and having the following problems:

macro will not run without being asked to from VBA interface
initially only sends rows 1-5 to sheet 2 (regardless of what row is given condition N in column 2)
subsequent transfer attempts only overlay on top of previous data

might just be being stupid somwhere but would be extremely greatful for any more advice.

cheers

**Palmetto** · 04-21-2010, 06:51 AM

The code I gave is just a basic approach based on the description in your thread and is, therefore, not tailored to your specific workbook and would have to be modified.

It would be best if you uploaded a sample workbook that both duplicates the exact structure of your real workbook and contains some data (non-sensitive). We could then tailore the code to the exact needs of your workbook.

**mjsmith127** · 04-21-2010, 08:32 AM

here is the attached workbook example, hope this works.

thanking you in advance.

ammended

**mjsmith127** · 04-22-2010, 04:22 AM

thanks for that, your a gentleman.

**mjsmith127** · 06-23-2010, 03:57 AM

Help!!!!

I've made some minor adjustments to my workbook and keep losing imformation.

If anyone can puzzle out my Visual Basic coding I would be most greatful.

Cheers.