Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Destination Code (2000)

    Does anyone have an idea why this does not work?

    <pre>Sub CopyEndingCounts()
    Dim qwe As Integer
    qwe = 1
    For Each cell In ThisWorkbook.Sheets("Data").Range("b29:af29")
    If cell.Value = "" Then
    qwe = cell.Column - 1
    Exit For
    End If
    Next
    If qwe < 2 Then qwe = 2
    ThisWorkbook.Unprotect password:=PW
    <font color=red>ThisWorkbook.Sheets("Data").Range(Cells( 29, qwe), Cells(38, qwe)).Copy _
    Destination:=ThisWorkbook.Sheets("Master").Range(" c3:c12")</font color=red>
    ThisWorkbook.Protect password:=PW
    End Sub
    </pre>


    I get "1004" Application or object defined error. When I change the part in red to use absolute cell references such as <pre> .Range("b29:b38"))</pre>

    it works fine. Is there some taboo about combining the two types of references in the same line?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Destination Code (2000)

    The first thing that I see is that the destination in your Copy method is a fixed range 9 columns wide. The source range could be up to 30 columns wide. Thirty columns will not fit into 9 columns.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Destination Code (2000)

    Afraid I do not understand Legare...in the example, the qwe should be the same for each item in the range...Range(Cells(29,qwe),Cells(38,qwe)) should, if qwe =2, refer to B29:B38. Is this not correct?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Destination Code (2000)

    Sorry, I didn't have my brain in gear when I read the code. Try changing that statement to:

    <pre> ThisWorkbook.Sheets("Data").Range(ThisWorkbook.She ets("Data").Cells(29, qwe), _
    ThisWorkbook.Sheets("Data").Cells(38, qwe)).Copy _
    Destination:=ThisWorkbook.Sheets("Master").Range(" c3:c12")
    </pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Destination Code (2000)

    Now Legare, that statement worked perfectly, but why the need to ID the range so specifically?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Destination Code (2000)

    If you don't specify the worksheet (and workbook) VBA assumes you meant the active workbook and the active sheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •