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]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object doesn't support this property or method


    Ahh


    Missed a period.

    Code
    1. With Sheets("RegionQualifiers")
    2. .Range("D2").AutoFill Destination:=.Range("D2" & ":D" & bottomA).End(xlDown)
    3. End With
  • Re: Object doesn't support this property or method


    I'm still getting the same error message.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object doesn't support this property or method


    Code
    1. Destination:=.Range("D2" & ":D" & bottomA).End(xlDown)


    This part looks strange
    try

    Code
    1. Destination:=.Range("D2", .Range("D2").End(xlDown))
  • 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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]

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object doesn't support this property or method


    Mumps,


    I couldn't find out the result in "RegionQualifiers". your sample is missing a sheet to finalise.


    So this is only my guess.

    Code
    1. With Sheets("RegionQualifiers")
    2. .Range("D2").AutoFill Destination:=.Range("D2:D" & .Range("A" & Rows.Count).End(xlUp).Row)
    3. End With
  • 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object doesn't support this property or method


    try chage to


    Code
    1. 'Copies Race name into column D in RegionQualifiers.
    2. Dim myDest As Range
    3. Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D" & Rows.Count).End(xlUp)(2)
    4. With Sheets("RegionQualifiers")
    5. Set myDest = .Range("a" & Rows.Count).End(xlUp).Offset(, 3)
    6. With .Range("d" & Rows.Count).End(xlUp)
    7. .AutoFill Sheets("RegionQualifiers").Range(.Cells, myDest), xlFillSeries
    8. End With
    9. End With
  • 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?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Object doesn't support this property or method


    change

    Code
    1. Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D" & Rows.Count).End(xlUp)(2)


    to

    Code
    1. Sheets("RegionQualifiers").Range("D" & Rows.Count).End(xlUp)(2).Value = Range("a2").Value
  • 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!

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.