Identify last cell data in a column and paste the same in all above cells

[aravindh8686]

Hi
I am searching for a VBA that will perform to copy the last cell data in a column "A" and paste the same above all the cells in that same column "A" in that same worksheet. Kindly help. I have provided a format here.

I need to do this operation for 100 + files . So kindly provide some automation to do this.


At present:

Column A
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015<-----Last cell data(copy it)

My requirement

Column A

015<-----Paste over all the cell in that column A
015
015
015
015
015
015
015
015
015
015
015
015
015
015

 

[Trebor76]

Hi aravindh8686,

I think this is what you're after:

Option Explicit
Sub Macro3()

    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("A1:A" & lngLastRow).Value = Range("A" & lngLastRow).Value

End Sub

Regards,

Robert

 

[ravishankar]

Create a file named A.xlsm with sheet named B. paste the following codes and save the file in the same folder containing your 100+ files to be modified.

Sub aravind()
Dim a As Long, C As String
Dim f As String, x As Integer, y As Integer
Workbooks("A.xlsm").Sheets("B").Activate
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
f = Dir(Cells(1, 2) & "*.xls*")
Cells(2, 1).Select
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
MsgBox "Listing is complete"
x = Sheets("b").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "there are " & x - 1 & " files"
    For a = 2 To x
    Workbooks("A.xlsm").Sheets("B").Activate
        If Cells(a, 1) <> "A.xlsm" Then
         Workbooks.Open Filename:=Cells(1, 2) & Cells(a, 1)
         Sheets(1).Range("A" & x).Copy
         Sheets(1).Range("A1:A" & x - 1).PasteSpecial
         ActiveWorkbook.Save
        ActiveWorkbook.Close
        End If
    MsgBox Cells(a, 1) & " updated"
    Next a
MsgBox "complete"
End Sub

on running the macro, it lists the filenames in col A, opens each, finds last row and copies it to all of col A saves and closes it. moves on to next file. I suggest you test the macro on a copy of your files till you convince yourself that it is working the way you want it. because the changes made will be saved.
Ravi shankar

 

[aravindh8686]

Thanks everyone...I got the desired output using your macros...