VBA Code to Copy to Bottom of Existing Data in Adjacent Field

Question

My VBA Macro code is supposed to insert Column C, add the heading and then type a formula into cell C2 =text(B2," mm/dd/yyyy ") I then want to copy and paste that formula to the bottom of the data. The problem is that the range changes with each spreadsheet. I do I code it to look at column B and go to the end of the data and copy the formula up to Row 2?

Just learning to do Macros and need to modify the script.

Here is my code:

Sub TEST1()
'
' TEST1 Macro
'

'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Revised Billed Date"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],"" mm/dd/yyyy "")"
    Range("C2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("C8").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("C7").Select
    Selection.End(xlUp).Select
    Range("C2").Select
End Sub

Wito · Accepted Answer · 2021-01-09 02:10:36Z

I believe that this will solve it for you. I will recommend you to learn to use With and End with to better organize and have a cleaner code. This will allow you to use .Range and .Paste .

All I did was pivot around the selected cells using .Offset(), place my self at the last row with content of column B, and create a range in column C in which I will lastly paste the content of the copied cell.

Sub TEST1()

With ActiveSheet

    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    
    .Range("C1").Select
    Selection.FormulaR1C1 = "Revised Billed Date"
    
    .Range("C2").Select
    Selection.FormulaR1C1 = "=TEXT(RC[-1],"" mm/dd/yyyy "")"
    
    Selection.Copy

    ''''''Find last cell with content in column B and go back to column C

    Selection.Offset(0, -1).End(xlDown).Offset(0, 1).Select
    
    '''''Create a range from the end of column C up until below C2 cell  

    .Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select

    .Paste

End With
End Sub

Collectives™ on Stack Overflow

VBA Code to Copy to Bottom of Existing Data in Adjacent Field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related