Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Paste (2000)

    I have run into a snag in running the following macro:
    Sub CopyPasteSpecial()
    Application.ScreenUpdating = False
    Range("A3:A61").Select '(The range I

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

    Re: Copy Paste (2000)

    All of this can be shortened to:

    Sub CopyPasteSpecial()
    Dim oSource as range
    Dim oTarget as range
    Application.ScreenUpdating = False
    Set oSource=Range("A3:A61") '(The range I
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste (2000)

    You'll run into problems is column B is still empty - your code will move to th elast cell in column B (B65536) and then try to move one row down, which is impossible. Try the following modified and shortened version:

    Sub CopyPasteSpecial()
    Application.ScreenUpdating = False
    Range("A3:A61").Copy
    Workbooks.Open Filename:="C:My DocumentsSpecialHandicaps.xls" '(The file I

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste (2000)

    Hans,
    Thank you for your immediate reply. The only problem is the macro stops at:
    Sheets("Scores").Range("B66536").End(xlUp).Offset( 1, 0).PasteSpecial _
    xlValues
    Any idea why?
    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste (2000)

    No, I tried it out on a test workbook and it worked OK. What is the error message you get?

  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 Paste (2000)

    Maybe the clipboard got emptied so there is nothing to paste?

    Try issuing the copy command after the workbooks.Open statement.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste (2000)

    Hans & Jan
    IInstead of: Sheets("Scores").Range("B66536").End(xlUp).Offset( 1, 0).PasteSpecial xlValues
    on 1 line I used: Sheets("Scores").select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlValues
    This seems to work but I don't know why



    At any rate, you guys got me aimed in the right direction.
    Thanks tons!
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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