Copy data from one to another sheet

[mt_1610]

Hi, I wrote this vba code tried to copy my data from Data Sheet to Target Sheet. With a condition, if a cell in column AC (Data Sheet) has value = 1 then cut entire row to the last row of Target Sheet. My code had an error that I couldn't figure it out. Could someone help me, please? Thank you.

Sub Copy_Data()

Dim Lrow As Long, LrowTarget As Long, c As Range

Application.EnableEvents = False

On Error Resume Next
Sheets("Data").Select
Lrow = Cells(Rows.Count, "AC").End(xlUp).Row
For Each c In Range("AC103:AC" & Lrow)
If c.Value = 1 Then

c.EntireRow.Cut
Sheets("Target").Select
LrowTarget = Cells(Rows.Count, "B").End(xlUp).Row
Cells(LrowTarget + 1, 1).Select
ActiveSheet.Paste

ElseIf c.Value = 0 Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub

 

[Michael M]

This should be quicker as you don't need to select sheets !

Sub MM1()
Dim lr As Long, lr2
lr = Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row
lr2 = Sheets("Target").Cells(Rows.Count, "B").End(xlUp).Row + 1
With Range("AC103:AC" & lr)
    .AutoFilter Field:=1, Criteria1:=1
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Target").Range("A" & lr2)
    .SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
    .AutoFilter
    .AutoFilter
End With
End Sub

 

[Michael M]

Sorry typo...

Sub MM2()
Dim lr As Long, lr2 As Long
lr = Sheets("Data").Cells(Rows.Count, "AC").End(xlUp).Row
lr2 = Sheets("Target").Cells(Rows.Count, "B").End(xlUp).Row + 1
With Range("AC103:AC" & lr)
    .AutoFilter Field:=1, Criteria1:=1
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Target").Range("A" & lr2)
    .SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
    .AutoFilter
    .AutoFilter
End With
End Sub

 

[mt_1610]

Thanks for your help. However, when I ran your code, it still had an error "We can't do that to a merged cell" with my excel file. How can I fix the error?

 

[Michael M]

Ahh, well you didn't say the cells were Merged.......VBA and merged cells will give you nightmares !!
Where is the merged cell / s ?
Try to eliminate the use of them wherever possible

 

[mt_1610]

My bad. I didn't know about that. The merged rows are 3, 4, 101, and 102 on Data Sheet and row 3, and 4 on Target Sheet.
Just clarified, start from row 103 in Data Sheet, I want to copy entire rows has value =1 in Column AC to the last row (Target Sheet), after that delete these entire rows has value =1 in Column AC (Data Sheet)
I'm appreciate it. Thank you!

 

[Michael M]

Which cells are merged in these rows....but if we are starting at row 103, there shouldn't be a problem.