Putting variable values in a second sheet

[Andim]

Hi everyone
I have a piece of code that I have put together myself (with help as it is my first). It is a command button that will take the highest number from a range of cells and put this into a variable then it will take a reference from another cell relating to the range. The idea next is to open another spreadsheet and find the match of this reference and offset along 2 cells and paste the highest number. Here is the code -

Private Sub CommandButton3_Click()
lm = ActiveWorkbook.Name
Dim ref As Range, gethighest As Range
Set ref = Worksheets("Sheet1").Range("A19")
Set gethighest = Worksheets("Sheet1").Range("A20:C23")
highest = Application.WorksheetFunction.Max(gethighest)

Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious Stuff\TCSpreadsheets\Convoluted.xls")

cd = ActiveWorkbook.Name
Dim cdrefcol As Range
Dim cellad As Range
Dim Cell As Object
Set cdrefcol = Worksheets("Sheet1").Range("A10:A12")

For Each Cell In cdrefcol
If Cell.Value = ref Then
If cellad Is Nothing Then
Set cellad = Range(Cell.Address)
Else
Set cellad = Union(cellad, Range(Cell.Address))
End If
End If
Next

cellad.Offset(0, 2).Value = highest

Workbooks(cd).Close SaveChanges:=True

End Sub

All of the code does what I want it to except the line in red. When it comes to pasting the highestnumbe in the correct cell it does it but on the wrong sheet (the first sheet lm instead of the second cd). I know it will be something simple but I just can't figure out why it is doing this. Any help will be very much appreciated.

Cheers
Andim

[gnod]

i think you need to specify the sheet name

[VBA]
Sheets("Sheet2").cellad.Offset(0, 2).Value = highest
[/VBA]

[Andim]

Hi

Tried this but it comes up with error saying Object doesn't support this method. I'm sure I need to specify the sheet as you say but everything I have tried comes up with this error and activating the sheet required doesn't help because it is already active.

[Andim]

Hi guys
I have figured it out so I thought I would paste the solution.
The line I highlighted was not where the problem was it was in the line

Set cellad = Range(Cell.Address)

it should say

Set cellad = Worksheets("Sheet1").Range(Cell.Address)

Thanks for looking