Help tweaking code to set variable

[samst]

Good Morning All

The follwing bit of Code (Thanks Tod!) Sets cell A2 in the sheet called "Temp" to a Variable. Then looks at the sheet called Data and looks for the column Header "Name" and if it finds it It pastes the contents of A2 into the first available row in in the column Name.

Here's my problem: The code only seems to work for the cell A2 how Can i make it work for the rest of the cell in column A? I've made multiple attempts and have been unuccessful. I also was not able to find anything on the board as I'm not really sure what to look up.

ANy thoughts would be appreciated. ANd please let me know if I didn;t make any sense.
Thanks a mill.



s1Value = Workbooks("Book1.xls").Worksheets("Temp").Range("A2").Value
Set FindHeading = Workbooks("Book1.xls").Worksheets("Data").Rows(1).Find("Hotel Name")
If Not FindHeading Is Nothing Then
Workbooks("Book1.xls").Worksheets("Data").Activate
ActiveSheet.Range(Cells(65536, FindHeading.Column) _
.Address).End(xlUp).Offset(1, 0).Value = s1Value
Else
On Error Resume Next
End If

 

[Cbrine]

This should do what you need. If will cycle through each cell within A2:A???(Last used cell in column A).

Hope this helps.

Dim R as range
for each r in activesheet.range("a2",activesheet.range("A65000").end(xlup))
Call your code
Next

 

[samst]

That's perfect! Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!