VBA to paste value in cell A14 to all below rows in column A

[Alexis1101]

Hello,

I have a spreadsheet with over 100 sheets. I need the value in Cell A14 of each sheet to be pasted to all rows in column A thru the end of the sheet. For example, I need Cell A14 (#A) to go all the way down to A29. The thing is, on each sheet there is a different number of rows. The data always begins in row 14, but I need column A to be pasted til the last row for each sheet regardless of the amount of rows. Also, sometimes there is only data in row 14 so nothing is necessary, but with it being over 100 sheets I would like a VBA that does this for the whole workbook.

 -> 

[#2]

@Alexis1101 

Try this macro:

Sub CopyDownA()
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A14").AutoFill Destination:=Range("A14:A" & LastRow)
End Sub

[#3]

Hey! Thank you so much for this.

This worked perfectly for the pulling down the data to the last row, but it does it in a sequence as opposed to pasting the same value that is in A14 down.

For example, if it was a number 1 in A14, the macro provided has the data below being 2,3,4, etc.

Any fix for this? I really appreciate your help!

[#4 · Accepted solution]

@Alexis1101 

New version:

Sub CopyDownA()
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A15:A" & LastRow).Value = Range("A14").Value
End Sub

[#5]

This worked!

My only question is is there a way to have it do the whole workbook? or selected sheets

[#6]

@Alexis1101 

A version that will loop through ALL worksheets in the active workbook:

Sub CopyDownAll()
    Dim Wks As Worksheet
    Dim LastRow As Long
    For Each Wks In Worksheets
        LastRow = Wks.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Wks.Range("A15:A" & LastRow).Value = Wks.Range("A14").Value
    Next Wks
End Sub

And one that will loop through the selected sheets only:

Sub CopyDownSelected()
    Dim Wks As Worksheet
    Dim LastRow As Long
    For Each Wks In ActiveWindow.SelectedSheets
        LastRow = Wks.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Wks.Range("A15:A" & LastRow).Value = Wks.Range("A14").Value
    Next Wks
End Sub