Copy range to blank cell

[ArenA777]

I want Vba to copy the column of data that is found under the name "Value" until it reaches a blank space and then copy that data in another worksheet. this is what I have so far.

Sub select_cellsCounty()
     Set cell = Cells.Find(What:="Value")      
     cell.Select
     Set Start= ActiveCell.Offset(1, 0).Select  
 Set ans = Application.InputBox(Start, Start, Type:=8)
        
        Range(ans, Cells(Rows.Count, ans.Column).End(xlUp)).Select
         
Selection.Copy
Sheets("State").Select
        Range("A2:A").Select
        ActiveSheet.Paste
End Sub

I want to get rid of the input box and have it do it automatically, but I dont know how to set the beginning of the range as the cell one below the name "Value". I'm using Excel 2013.


Thanks,

[Bob Phillips]

What is the Inputbox for, you are finding the column.

[ArenA777]

The inputbox was used initially to select the first cell in the range, but i want to get rid of that.
I want something like this

Range(cells.Find(What:="Value").Offset(1, 0), cells(Rows.Count, ans.Column).End(xlUp)).Select

Where the cell below "Value" is the first cell in the range, and the range is selected until the first empty cell. I am trying this but it doesnt allow me to do it.

[SamT]

Dim Found As Range
Set Found = Rows(1).Find(What:="Value").Offset(1, 0)
Range(Found, Cells(Rows.Count, Found.Column).End(xlUp)).Copy

Possible Alternate, If you can guarantee that the column contains no empty cells above the bottom most used cell

Range(Found, Found.End(xlDown)).Copy

[ArenA777]

Dim Found As Range
Set Found = Rows(1).Find(What:="Value").Offset(1, 0)
Range(Found, Cells(Rows.Count, Found.Column).End(xlUp)).Copy

Possible Alternate, If you can guarantee that the column contains no empty cells above the bottom most used cell

Range(Found, Found.End(xlDown)).Copy

I tried what you said, but I keep getting an "object variable or With block variable not set" error.

Is there anyway to avoid the error

[mdmackillop]

Sub Test()
Dim Found As Range
Set Found = Cells.Find(What:="Value").Offset(1, 0)
Range(Found, Found.End(xlDown)).Copy Sheets("State").Range("A2")
End Sub

[ArenA777]

Thank You, that worked perfectly

[mdmackillop]

Happy to help. For the future, please use meaningful titles rather than "Please Help" and the like. I'll change this title.

[SamT]

JSMH

[Paul_Hossler]

JSMH

?

[mdmackillop]

Didn't know either!