Forum Discussion

Copper Contributor

Aug 10, 2022

Solved

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 g...

Aug 10, 2022

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

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

Alexis1101

Copper Contributor

Aug 10, 2022

This worked!

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

HansVogelaar

MVP

Aug 10, 2022

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