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 Sheets("sheet24").Activate erow = Sheet24.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Sheets("sheet24").Range(Cells(erow, 1), Cells(erow, 2)) Sheets("sheet1").Activate End If Next i Application.CutCopyMode = False End Sub
Thanks in Advance