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

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

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