CODEWITHSUNDEEP
vbaexcel
Chandrasekar R
Chandrasekar R

Reputation: 123

Search for header, copy paste value till last row

I have created a macro to search for header and Copy the header and paste it till the last row of that particular column. But when I do it I have to specify the column which i dont want . But I need to paste it with the Header search in the same column till last row. Ex:Total is the Header name in BV column. Please assist.

Range("A1").Select
Dim rngUsernameHeader As Range
Dim rngHeaders As Range
Set rngHeaders = Range("1:1")
Set rngUsernameHeader = rngHeaders.Find(what:="Total", After:=Cells(1, 1))
rngUsernameHeader.Copy
lastrow = Range("A65536").End(xlUp).Row
**ActiveSheet.Paste Destination:=Range("BV1:BV" & lastrow)**
Selection.End(xlUp).Select
Application.CutCopyMode = False

Upvotes: 0

Views: 626

Answers (3)

DisplayName
DisplayName

Reputation: 13386

if you want to paste the content of row 1 cell containing "Total" in the found cell column from row 1 down to the row corresponding to column A last not empty one, then use:

Sub main()
    With Range("1:1").Find(what:="Total", After:=Cells(1, 1))
        .Resize(Cells(Rows.Count, 1).End(xlUp).Row).Value = .Value
    End With
End Sub

or, if you know that "Total" is the whole content of the header

Sub main()
    Range("1:1").Find(what:="Total", After:=Cells(1, 1)).Resize(Cells(Rows.Count, 1).End(xlUp).Row).Value = "Total"
End Sub

while, if you want to paste the content of row 1 cell containing "Total" in the found cell column from row 1 down to the last not empty cell of that same column, then use:

Sub main2()
    With Range("1:1").Find(what:="Total", After:=Cells(1, 1))
        .Resize(Cells(Rows.Count, .Column).End(xlUp).Row).Value = .Value
    End With
End Sub

Upvotes: 0

SebastianG
SebastianG

Reputation: 26

is this what you are looking for?

Range("A1").Select
Dim rngUsernameHeader As Range
Dim rngHeaders As Range
Set rngHeaders = Range("1:1")
Set rngUsernameHeader = rngHeaders.Find(what:="Total", After:=Cells(1, 1))
rngUsernameHeader.Copy
lastrow = Range("A65536").End(xlUp).Row
ActiveSheet.Paste Destination:=Range(rngUsernameHeader, rngUsernameHeader.Offset(lastrow - 1))

Selection.End(xlUp).Select
Application.CutCopyMode = False

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

Just build your paste range from rngUsernameHeader

ActiveSheet.Paste Destination:=Range( _
  Cells(1, rngUsernameHeader.Column), _
  Cells(lastrow, rngUsernameHeader.Column))

Upvotes: 1

Related Questions