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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,840
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
11,840
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
11,840
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
11,840
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,085
Messages
5,857,273
Members
431,867
Latest member
Dalorian

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