424 Error - Object Required

[tbucci]

Hi, these procedures belong to a rather long process, so I've pulled out only what I think is applicable. The first two procedures work fine. The second two procedures should work in the same way, only they don't. Instead, I receive a 424 on line 1 of Sub TransferAssociate(MyCell).

So far nothing I've researched seems to address this. So frustrating! Are there any thoughts about what I might be missing?

Thanks!

Dim TaskCell As Range<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim MyCell As Range
<o> </o>
Sub TransferData(Year)
<o> </o>
'Loops through each cell consecutively under the "Region" heading.<o></o>
'Calls TransferEntry for specific Region
<o> </o>
Workbooks("PMO Training Log.xls").Activate<o></o>
Worksheets(Year).Activate<o></o>
Range(Range("A1:P3").Find("Region").Offset(RowOffset:=1).End(xlDown), _<o></o>
Range("A1:P3").Find("Region").Offset(RowOffset:=1)).Select<o></o>
For Each TaskCell In Selection<o></o>
If TaskCell.Value = Region Then _<o></o>
Call TransferTaskEntry(TaskCell)<o></o>
Next TaskCell
<o> </o>
End Sub<o></o>
<o> </o>
Sub TransferTaskEntry(TaskCell)<o></o>
<o> </o>
'Copies the appropriate cells in the Year sheet and pastes them in the next row on Training Report<o></o>
<o> </o>
Range(TaskCell.Offset(ColumnOffset:=-1), TaskCell.Offset(ColumnOffset:=11)).Select<o></o>
Selection.Copy<o></o>
Workbooks("Training Report.xls").Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(RowOffset:=1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
<o></o>
End Sub
<o> </o>
Sub CreateIndividualWB
<o> </o>
Workbooks("Training Report.xls").Worksheets("Sheet1").Activate<o></o>
Range(LastCell.End(xlUp), LastCell).Select<o></o>
<o></o>
For Each MyCell In Selection<o></o>
If MyCell.Value = Associate Then _<o></o>
TransferAssociate (MyCell)<o></o>
Next MyCell
<o> </o>
End Sub
<o> </o>
<o> </o>
Sub TransferAssociate(MyCell)<o></o>
<o></o>
Range(MyCell, MyCell.Offset(ColumnOffset:=12)).Select<o></o>
Selection.Copy<o></o>
NewAssociateBook.Worksheets("Sheet1").Activate<o></o>
Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(RowOffset:=1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>
:=False, Transpose:=False<o></o>
<o> </o>
End Sub

 

[Anthony47]

May be

If MyCell.Value = Associate Then _
TransferAssociate (MyCell.Address)

Bue

 

[tbucci]

Yes, this does change from value to address, but unfortunately I still receive the 424 error in the same place.

 

[Anthony47]

And now:

Range(MyCell, Range(MyCell).Offset(ColumnOffset:=12)).Select

Or

Range(MyCell).Resize(1, 12).Select

And note that ".select" is unnecessary, you may use directly Range(MyCell).Resize(1, 12).copy
Bye

 

[tbucci]

This works great, and thanks so much for the tip!