Solved: Copying cell content if target cell is blank

[KoE]

Greetings,

I'm looking to copy cell content from cells in column D into column O if target cell is blank.

I tried
=IF (O2="";D2;O2) but that doesn't work. I was told that I need vba to do this as it involves circular referencing but I know almost nil about vba.

Sample data as attached.

Thanks! :bow:

[AZIQN]

Hi KoE, try this:


Sub COPYDO()
Application.ScreenUpdating = False
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, "O").Value = "" Then
.Cells(i, "O").Value = .Cells(i, "D").Value
End If

Next i
End With
Columns("O:O").Select
Selection.WrapText = True
Application.ScreenUpdating = True
End Sub

[KoE]

AZIQN,

awesome! thank you very much :friends:

[KoE]

The above code works well for a single worksheet but not when i tried to span across several selected sheets.

I changed With-End construct with For Each-Next and I got stuck there :(

I'd be highly grateful if someone would point to my mistakes.

Below are the errorneous codes:


Option Explicit
Sub CopyPosting()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim i As Long
Dim LastRow As Long
On Error GoTo 0

For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If Cells(i, "O").Value = "" Then
Cells(i, "O").Value = Cells(i, "D").Value
End If

Next i

Columns("O:O").Select
Selection.WrapText = True
Application.ScreenUpdating = True

Next
End Sub


Thanks!

p/s - i've made a cross-posting on another board as well & will update accordingly

[KoE]

Folks,

Here are the functional codes to cater for multiple sheet. It'll copy cell content from a different source and paste it into target cell (within the same sheet) if the latter is blank/empty. In my case, D is my source column and O is my target.



Sub CopyPosting()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim i As Long
Dim LastRow As Long
On Error GoTo 0

For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
With sh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "O").Value = "" Then
.Cells(i, "O").Value = .Cells(i, "D").Value
End If
Next i
End With

Columns("O:O").Select
Selection.WrapText = True
Application.ScreenUpdating = True

Next
End Sub


Many thanks for those who had helped me :bow: