Need help to copy a call value in all rows of a dynamic sized table

[karl_learnvba]

Hello, I started learning VBA yesterday,.. please bear with me if this is v basic,....

I have a set of data in a worksheet that will vary in size.

i would like to copy the value in cell P2 (10.10.2019) down to the bottom of the column,.. which is currently 4000 rows long.

[TABLE="width: 322"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Column P[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Number[/TD]
[TD]Exp Close Date[/TD]
[TD]Report runDate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]December 31, 2020[/TD]
[TD]10.10.2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]February 28, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]September 30, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]December 21, 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]October 31, 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]November 30, 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]January 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]March 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]December 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]March 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]July 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]December 15, 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]June 26, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]June 01, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]September 30, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]March 31, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD="align: right"]June 30, 2020[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lastrow[/TD]
[TD="align: right"]December 02, 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you all in advance

 

[Anthony47]

Not sure which is the layout of your data.
You need a macro, for example:

Sub CopyDown()
Dim CopyWhat As String, CloseCol As String, LastClose As Long
'
CopyWhat = "P2"         '<<< Cell to copy down
CloseCol = "M"          '<<< The column "Exp Close Date"
'
LastClose = Cells(Rows.Count, CloseCol).End(xlUp).Row
Range(CopyWhat).Copy Range(CopyWhat).Offset(1, 0).Resize(LastClose - Range(CopyWhat).Row + 1, 1)
End Sub

You have to modify the lines marked "<<<" according the comment
The code has to be inserted in a Standard Module of your vba; if you need furher instruction don't hesitate to ask

Bye

 

[karl_learnvba]

Thank you Anthony. Works.
Will study and learn.

Thanks !

 

[Jon Peltier]

No code required.

Select P3 and press Ctrl+D to copy the value there from P2.

Select P2:P3, then double click on the fill handle, that small dark square at the bottom right corner of the box that highlights the selection. This will fill down as far as you have data in column O.

 

[Anthony47]

[...]
Select P3 and press Ctrl+D to copy the value there from P2.

@John: my Excel is not in English, and shortcut Contr+D is used for something else; so I was not able to test how your solution works in case that the driving column is not the one at the left of the target one.

@Karl: Another option without macro is transforming the table in an "Excel Table":
-select leftmost header; Contr-Shift-DownArrow to extend to the end; extend to the right
-Menu /Insert /Table; select "With header"; confirm with Ok

Now if you write in P2 the value in the form of a formula, eg =DATE(2019,10,11), then it will propagate up to the end of the table.

Bye

 

[Jon Peltier]

Ctrl+D is D for "Down", maybe your localization has a similarly familiar keystroke to try.

Anyway, if Ctrl+D doesn't work, copy P2 and paste it into P3, then select P2:P3 and double click the magical fill handle.