Search column header and copy

[Cinema]

Hi,


I want to search in Sheet1 a specific column Header ("Prices") and copy the values in the column (not entire column with empty cells) and paste it in Sheet 2 in Cell("C6").

[SamT]

thread moved from Announcements Folder.

[mancubus]

Entire column (from 2nd row to last row with data):


Sub vbax_54618_Find_Header_Col_Num_Copy_Column() Dim FoundCell As Range
Dim RowNum As Long
With Worksheets("Sheet1")
Set FoundCell = .Rows(1).Find("Prices", , , , xlByColumns, xlNext)
RowNum = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - 1 '-1 for header
End With

Worksheets("Sheet2").Range("C6").Resize(RowNum).Value = FoundCell.Offset(1).Resize(RowNum).Value
End Sub


Nonblank cells:



Sub vbax_54618_Find_Header_Col_Num_Copy_NonBlanks()
Dim FoundCell As Range
With Worksheets("Sheet1")
Set FoundCell = .Rows(1).Find("Prices", , , , xlByColumns, xlNext)
.Cells(1).AutoFilter Field:=FoundCell.Column, Criteria1:="<>"
With .AutoFilter.Range
.Columns(FoundCell.Column).Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Worksheets("Sheet2").Range("C6")
End With
End With
End Sub

[Cinema]

Hi,
thank you. I did it like this:
Col = Application.Match("Prices", Sheets(Sheet1).Rows(1), 0)
Sheets(Sheet1).Cells(1, Col).Offset(1).Resize(500000).Copy _
Sheets(Sheet2).Range("C6")


Thank you very much.

[jolivanes]

Why 500,000? Why not 1,048,575?
mancubus gave you an example on how to do it properly. Your way you're going to run into problems.
Can't wait for you to come back and ask for how to make a file smaller because it has gotten too large to work with.