• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and Paste Rows Automatically if Condition Met

AlanSidman

Well-Known Member
Remove all data from sheets other than Mainboard in Column A.

Here is a workable VBA solution

Code:
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

New Member
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?
 

Attachments

AlanSidman

Well-Known Member
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
 
Top