Copy and Paste Rows Automatically if Condition Met

[Anie Zue]

Hi,

Can someone help me create a code where i can copy data from mainboard and paste to another sheet according to months? Attached herewith my data.

Thank you so much your help.

 

[AlanSidman]

Remove all data from sheets other than Mainboard in Column A.

Here is a workable VBA solution

Option Explicit

Sub MoveData()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet, s4 As Worksheet, s5 As Worksheet
    Set s1 = Sheets("MainBoard"): Set s2 = Sheets("Jan-Mac")
    Set s3 = Sheets("Apr-Jun"): Set s4 = Sheets("Jul-Sep")
    Set s5 = Sheets("Oct-Dis")
    Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long
    Dim i As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 5 To lr1
        With s1
            If Month(.Range("B" & i)) = 1 Or Month(.Range("B" & i)) = 2 Or Month(.Range("B" & i)) = 3 Then
                lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
                .Range("A" & i & ":I" & i).Copy
                s2.Range("A" & lr2).PasteSpecial xlPasteValues
            ElseIf Month(.Range("B" & i)) = 4 Or Month(.Range("B" & i)) = 5 Or Month(.Range("B" & i)) = 6 Then
                lr3 = s3.Range("A" & Rows.Count).End(xlUp).Row + 1
                .Range("A" & i & ":I" & i).Copy
                s3.Range("A" & lr3).PasteSpecial xlPasteValues
            ElseIf Month(.Range("B" & i)) = 7 Or Month(.Range("B" & i)) = 8 Or Month(.Range("B" & i)) = 9 Then
                lr4 = s4.Range("A" & Rows.Count).End(xlUp).Row + 1
                .Range("A" & i & ":I" & i).Copy
                s4.Range("A" & lr4).PasteSpecial xlPasteValues
            Else
                lr5 = s5.Range("A" & Rows.Count).End(xlUp).Row + 1
                .Range("A" & i & ":I" & i).Copy
                s5.Range("A" & lr4).PasteSpecial xlPasteValues
            End If
        End With
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Action Completed"

End Sub

 

[Anie Zue]

I worked. But if i try keyin new data, why it doesnt work? Not automatically copy and paste? The numbers in column b and c, what are those? why it didnt display the date?

 

[AlanSidman]

This will require a change event. Which column in the original data do you wish to have trigger the event. This will be the column that you enter data into last. This is a case of not asking in the first post exactly what you want to happen. You never mentioned that you want it to happen when you enter data. This was your request and it was supplied as asked

help me create a code where i can copy data from mainboard and paste to another sheet according to months