Object doesn't support this property or method

I am writing some code to try to do the following:
- copy a cell without any formatting (no style, no colour) from the active sheet to the first blank cell in column D in a sheet called RegionQualifiers
- then to Fill Down this cell in column D to the last used row in column A

I get the error message : "Object doesn't support this property or method." on the line starting with AutoFill.

I would like to replace "D2" (both occurrences) with some code to find the first blank cell in column D so that if I run the macro more than once, the Copy and AutoFill start on the first blank line. Any help would be very greatly appreciated.

[VB]
Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D2")
Dim bottomA as Long
bottomA = Range("a" & Rows.Count).End(xlUp).Row
With Sheets("RegionQualifiers")
.AutoFill Destination:=Range("D2" & ":D" & bottomA).End(xlDown)
End With
[/VB]

Re: Object doesn't support this property or method

Thank you jindon. Unfortunately, I get the message "AutoFill method of range class failed" on the Autofill line.

Re: Object doesn't support this property or method

I'm still getting the same error message.

Re: Object doesn't support this property or method

The FillDown worked but it went all the way down to the bottom of the sheet. It didn't stop at the bottom of Column A.

Re: Object doesn't support this property or method

I'm sorry jindon but I have to step away from my computer for a few hours. When I get back I will send you a copy of the worksheets and give you a more detailed description. It might be easier if you can see what I'm working with. Thank you so much for your help and your patience.

Re: Object doesn't support this property or method

Thank you and don't stay up too late!

I have attached the file. I would like to copy Range("A4:D25") from the Boys8 sheet to the first blank row of the RegionQualifiers sheet. Then I would like to copy Range("A2") to each cell in column D in the RegionQualifiers sheet but only for the same number of rows as was in the first range copied ("A4:D25"). I would like to copy only the contents of A2 not the formatting like style and colour. When I run the macro again with the Boys7 sheet active, I want the same ranges copied but below the ranges copied from the Boys8 sheet. Here is the complete code of what I want to do. This part of a larger macro. Everything works well except for the last part that I've described above.
I've attached 2 files. Use the larger file. I sent the smaller file but it was missing some data. I didn't know how to delete it.

[VB]
'Copies top 22 runners into RegionQualifiers
Dim TeamRng As Range
Set TeamRng = Range("A4:C25")
TeamRng.Select
Selection.Copy Destination:=Sheets("RegionQualifiers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

'Finds third team runner outside of top 22 runners.
Dim bottomC As Long
Dim bottomH As Long
bottomC = Range("c" & Rows.Count).End(xlUp).Row
bottomH = Range("h" & Rows.Count).End(xlUp).Row

For r = 4 To bottomH
If Range("G" & r) = "1" Then FirstTeam = Trim(Range("H" & r))
If Range("G" & r) = "2" Then SecondTeam = Trim(Range("H" & r))
Next r

Dim Counter As Integer
Counter = 0
For r = 3 To bottomC
Set FirstCell = Range("A" & r)
Set LastCell = Range("D" & r)
If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter = 3 _
Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
Next r

Counter = 0
For r = 3 To bottomC
Set FirstCell = Range("A" & r)
Set LastCell = Range("D" & r)
If Range("A" & r).Offset(0, 2) = SecondTeam Then Counter = Counter + 1
If Range("A" & r).Offset(0, 2) = SecondTeam And LastCell > 22 And Counter = 3 _
Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
Next r

Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D2")
With Sheets("RegionQualifiers")
.Range("D2").AutoFill Destination:=.Range("D2", .Range("D2").End(xlDown))
End With
[/VB]

Re: Object doesn't support this property or method

Hi jindon:

Your suggestion worked very well except that when I run the macro on a different sheet, the data copied from the previous sheet is overwritten. I want to run the macro on several sheets so that the data is added to what is already there. I modified my posting because I attached a file that was missing the sheet you mentioned. Please look at my very last posting to get a better idea of what I am doing and use the larger of the two files. It has the missing sheet.

Many thanks.

Re: Object doesn't support this property or method

Sorry jindon. Try this attachment. The reason for the errors on the FinalStandings sheet is because I have deleted some sheets to make the file size smaller.

Re: Object doesn't support this property or method

You do beautiful work!!!! It worked like a charm. Thank you so much. Do you have any suggestions as to how I can copy "A2" without the formatting like the cell colour?

Re: Object doesn't support this property or method-PROBLEM SOLVED

It works perfectly every time. I can't tell you how much I appreciate all the time and assistance you have given me. Thank you, thank you, thank you!