Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Paste from VBA

    Hi.

    Firstly i point out i am running this code from within Access 97.

    I want to take the contents of a cell in every other row, and paste them into a cell one row above.

    The following code selects every other row correctly, but at the selection.copy line errors out with an Error 4605, "This method or property is not available because no text is selected".

    For Each rng In sht.UsedRange.Rows

    If rng.row Mod 2 <> 0 Then
    sht.Cells(rng.row, 5).Select
    Selection.Copy
    sht.Cells(rng.row - 1, 2).Paste
    End If

    Next rng

    I know that the cell does contain text, so the error should read something like "Error. Your code is wrong."

    Something simple i am sure, but where is my error???

    Many thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    Have you set a reference to Excel in your code. The fact that rng.row is not changed to rng.Row (capitalised R), indicates that there might be a problem (assuming you copied the code as is from Access).

    I think a similar problem arose recently in a thread involving yourself, and including a reference to Excel seems to have solved it.

    see <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=45502&page=&v iew=&sb=&vc=1#Post45502>Here</A>

    Dim rng As Excel.Range ?

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    Hi Andrew

    Yes, i have set a reference to Excel, in fact, this bit of code is in the same procedure as the other code you refer to.

    In addition, when i step through the code, the cursor indicates the correct row number when held over rng.row, so that bit is working, and furthermore, if the row number is such that it should be skipped, it is skipped, so the Mod line is definately OK.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Copy/Paste from VBA

    Hi Peter,
    Try changing your code to:
    If rng.row Mod 2 <> 0 Then
    sht.Cells(rng.row - 1, 2) = sht.Cells(rng.row, 5)
    End If
    and see if that helps. As a general rule, your code will run faster if you can avoid selecting, copying and pasting wherever possible.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    <hr>As a general rule, your code will run faster if you can avoid selecting, copying and pasting wherever possible.<hr>
    And it can also stop your workbook jumping around like Rory's userpic!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    I usually stop the screen jumping with a:
    Application.screenupdating = False

    Then when the fireworks are over:
    Application.screenupdating = true

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    Hopefully in addition to, and not in place of, using ranges instead of selections (were possible).

    It certainly does make code faster with ranges.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Barnsley, Yorkshire, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste from VBA

    Well, as usual, i have to say a big THANK YOU to those who have helped me :-)

    One other little problem: The workbook i am working on is saved under a different name, and then i have the following lines:

    appExcel.Application.Visible = True
    appExcel.Quit
    Set appExcel = Nothing

    I am assuming these lines are correct.

    If i then double click on the file to open it, it appears to open in "invsible" mode, i.e. i can't see Excel. But, if i open any other Excel file, then, when Excel opens, i can see both files, including the one that appeared to be invisible???

    I am presuming i have not reset or closed something properly...??

Posting Permissions

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