VBA macro code to move data from one Excel Worksheet to another Worksheet within the same Workbook

[Win7]

Hi All,

I am a beginner in VBA Excell scripting.

My requirement is as follows:

I have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .

The 'MainDataSheet' has 5 columns and one of the column is 'status'.

The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.

Can anyone please help me with the code.

Thanks,

Win7

 

[Win7]

Thank you so much VoG it works Now.

Regards,

Win7

 

[Danron]

Is there a way to make the macro move the rows if there is any date in the column instead of the specific word move?

 

[VoG]

Yes. Try "<>" as Criteria1

 

[Danron]

It worked but...

I have a list on "Customer Contact" sheet and when anything is put into the list in Column G I want the macro to move only that row to the list on the next sheet "Inspection".

 

[VoG]

Try this: right click the Customer Contact sheet's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 7 And Target.Value <> "" Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Inspection").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

 

[Danron]

Thank you for your help btw but It did not work. It took all 4 rows including the 2 that did not have any information in column 7 or G. Also it seems to copy the entire row instead of just columns A-G in that row.

Currently I have the macro

Sub Shift()
Dim r As Range, LR As Long
With Sheets("Customer Contact")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    Set r = .Range("A2").Resize(LR - 1)
    .Range("A1").AutoFilter field:=5, Criteria1:="<>"
    .Range("A1").AutoFilter field:=4, Criteria1:="<>"
    With r.SpecialCells(xlCellTypeVisible).EntireRow
        .Copy Destination:=Sheets("Inspection").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    .Range("A1").AutoFilter
End With
With Sheets("Inspection")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:E" & LR).Sort Key1:=.Range("A2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

and also put your last suggestion into the Customer Contact sheet view code as

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 7 And Target.Value <> "" Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Inspection").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

 

[VoG]

You need to use one or the other. The second code will run automatically when you enter anything in column G. If you want to 'move' data from A:G presumably you want to delete that row of data in A:G after copying. What should happen to columns to the right of G in that row?

 

[Danron]

Preferably nothing will be typed beyond column 7 but I guess if something is then it should move with the row so I am not to concerned about that.

It works when I type it in manually but currently I have a list that can be filled in by accessing the data menu then form. When the form is filled out all the information is put in the list including the information in column 7 but it does not move to the next sheet unless you delete and type column 7 in manually. Is there a way to make it move from the list if the data form puts it in the list?

Also, when it moves to the next page it is pasted below the list that I have in the Inspection sheet. Is there a way to make it move from list to list?

 

[VoG]

What exactly do you mean by a list?

 

[Danron]

The list that are created through excel by selecting columns 1-7 then go to the data menu select list, then create list (or ctrl+L). That way row 1 is the column title and everything below the list is in alphabetical order.