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

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.

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

HansVogelaar
MVP
Aug 10, 2022
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
- Alexis1101
  Copper Contributor
  Aug 10, 2022
  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!
  - HansVogelaar
    MVP
    Aug 10, 2022
    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

Forum Discussion

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

Share

Resources