I'm not sure if I got it exactly right but these changes should help. I
may of not understood what the data in rows 13 and 15 look like. I
added to one of the Find methods the after property so you can look for
the week number after a certain column.

Sub MoveData()

Set sourcesht = Workbooks("Book1.xls").Sheets("Sheet1")
Set DestSht = Workbooks("Book2.xls").Sheets("Sheet1")

With sourcesht
Person = .Range("A1")
Estworkload = .Range("C4")
Realworkload = .Range("C5")
WeekNum = .Range("F2")

End With

With DestSht
Set c = .Range("A16:A35").Find(what:=Person, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set StartCol = .Rows(13).Find(what:="Estworkload", _
LookIn:=xlValues, lookat:=xlWhole)
If StartCol Is Nothing Then
MsgBox ("Cannot find : ESTworkload")
Else
Set c1 = .Rows(15).Find(what:=WeekNum, _
after:=.Cells(15, StartCol.Column - 1), _
LookIn:=xlValues, _
lookat:=xlWhole)

If Not c2 Is Nothing Then
.Cells(c.Row, c2.Column) = Estworkload
End If
End If
Set StartCol = .Rows(13).Find(what:="realworkload", _
LookIn:=xlValues, lookat:=xlWhole)
If StartCol Is Nothing Then
MsgBox ("Cannot find : ESTworkload")
Else
Set c1 = .Rows(15).Find(what:=WeekNum, _
after:=.Cells(15, StartCol.Column - 1), _
LookIn:=xlValues, _
lookat:=xlWhole)

If Not c2 Is Nothing Then
.Cells(c.Row, c2.Column) = Realworkload
End If
End If
End If
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=163315

Microsoft Office Help