Search for header, copy paste value till last row

Question

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

You question is a little hard to follow, partially because you did not include any sample data or desired output. Are you trying to put a value in the first empty cell at the bottom of a column that matches a column name? If so, `MATCH` is probably an easier way to get the column number. — ashleedawg, Jun 06 '18 at 05:15
**ActiveSheet.Paste Destination:=Range("BV1:BV" & lastrow)**. Instead of mentioning column name here, I would require to paste with Header name as mentioned in the question. — Chandrasekar R, Jun 06 '18 at 05:19

score 1 · Answer 1 · answered Jun 06 '18 at 05:19

1

Just build your paste range from rngUsernameHeader

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

answered Jun 06 '18 at 05:19

chris neilsen

52,446
10
84
123

[You might want to read this](https://stackoverflow.com/q/10714251/445425) – chris neilsen Jun 06 '18 at 05:19

score 1 · Accepted Answer · answered Jun 06 '18 at 05:27

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

score 0 · Answer 3 · answered Jun 06 '18 at 08:37

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

Search for header, copy paste value till last row

3 Answers3