VBA code to copy one cell to another

bharned3

New Member
Joined
Jan 23, 2014
Messages
23
I want some VBA code to do something like this

IF column D is null then copy the data that is in Column P
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,118
Do you want to copy and then paste it somewhere? If so, where?
 

bharned3

New Member
Joined
Jan 23, 2014
Messages
23
Do you want to copy and then paste it somewhere? If so, where?

Yes Sorry
IF column D is null then copy the data that is in Column P and paste it back in column D...

To take it even further I could then delete column P
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,118
Try:
Code:
Sub CopyCell()
    Application.ScreenUpdating = False
    Dim bottomD As Long
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("D2:D" & bottomD)
        If rng = "" Then
            rng = Range("P" & rng.Row)
            Range("P" & rng.Row).ClearContents
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 

bharned3

New Member
Joined
Jan 23, 2014
Messages
23

ADVERTISEMENT

Sweet I think that worked, although it came over as a number. Is there a way to bring the formatting or apply a date format after
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,118
Try this one:
Code:
Sub CopyCell()
    Application.ScreenUpdating = False
    Dim bottomD As Long
    bottomD = Range("D" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("D2:D" & bottomD)
        If rng = "" Then
            Range("P" & rng.Row).Copy
            Range("D" & rng.Row).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            Range("P" & rng.Row).ClearContents
        End If
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 

bharned3

New Member
Joined
Jan 23, 2014
Messages
23

ADVERTISEMENT

Thanks so much that worked....
 

bharned3

New Member
Joined
Jan 23, 2014
Messages
23
okay I hit a bug, It is not copying or deleting the last 2 rows?

I am calling this code 3 times for different columns and it works great on all except the last 2 rows. after that step I also delete another column, if I try to delete the columns P (with VBA) where the data exists that did not get copied it doesn't go away either.

hope that makes sense..

Thanks again for the help
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,118
It would be hard to diagnose without seeing your actual file. Could you upload your file to a free site such as www.box.com where after marking it for sharing, you can get a link to the file which you can post here. Also clarify how you are using it for different columns and why you are deleting certain columns.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,913
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top