View Full Version : Solved: Copying cell content if target cell is blank
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
03-31-2009, 02:27 PM
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
AZIQN,
awesome! thank you very much :friends:
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
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.