I think it's a simple thing but I am not able to do this. Hope someone help.
I have a datasheet name Source where a column data starts AI9 to AI1200 mixed with blanks and nonblank cells,
I want to copy the data (Skip the blank cell which has a formula) to another sheet name Destination.
The thing is if anything changes to AI9 to A1200, It reflects the destination sheet Automatically.
I mean suppose previously a blank cell now has data it updates to the destination sheet automatically.
I am trying to use the below code but nothing happens
- Sub copyNonBlankData()
- Dim erow As Long, lastrow As Long, i As Long
- lastrow = Sheet4.Cells(Rows.Count, 35).End(xlUp).Row
- For i = 9 To lastrow
- If Sheet4.Cells(i, 1220) <> "" Then
- Sheets("sheet4").Range(Cells(i, 34), Cells(i, 2)).Copy
- erow = Sheet24.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
- ActiveSheet.Paste Destination:=Sheets("sheet24").Range(Cells(erow, 1), Cells(erow, 2))
- End If
- Next i
- Application.CutCopyMode = False
- End Sub
Thanks in Advance