+ Reply to Thread
Results 1 to 3 of 3

copy paste problems

Hybrid View

gpp copy paste problems 06-17-2008, 03:48 AM
Simon Lloyd This is probably what you... 06-17-2008, 04:46 AM
gpp thanks 06-18-2008, 03:49 AM
  1. #1
    Registered User
    Join Date
    06-17-2008
    Posts
    2

    copy paste problems

    I have lots of problems opening a remote workbook and copying parts of a sheet, I have tried three versions but nothing works reliably:

    Sub pfdbPlainImport()
    
        With Application
            .Calculation = xlManual
            .DisplayAlerts = False
            .RTD.ThrottleInterval = 100000     'slow down bloomberg real time data
        End With
    
        ....
    
        Dim WB As Workbook
        Set WB = Workbooks.Open("X:\40-PF\PFDB\PFDB_today\PM.xls")
        
        Dim WS As Worksheet
        Set WS = WB.Sheets("PM_data_short_SAPNbr")
    
        'copy code version 1:   (fails occasionally on paste with run time error)
        
        WS.Activate
        WS.Range("A2").Resize(10000, 42).Select
        Selection.copy
        pfdb.Activate
        pfdb.Range("A2").Resize(10000, 42).Select
        pfdb.Paste
        WB.Close (False)
    
        'copy code version 2:   (fails occasionally with run time error)
    
        WS.Range("A2").Resize(10000, 42).copy
        pfdb.Range("A2").Resize(10000, 42).PasteSpecial Paste:=xlValues
    
        'copy code version 3:   (fails occasionally with run time error)
    
        WS.Range("A2").Resize(10000, 42).copy _
        Destination:=pfdb.Range("A2").Resize(10000, 42)
    I need to copy only the values. is there a better way of doing this.
    Thanks.
    Last edited by VBA Noob; 06-17-2008 at 03:54 AM.

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    This is probably what you need:
    WS.Activate
        WS.Range("A2").Resize(10000, 42).Copy
        pfdb.Activate
        pfdb.Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        WB.Close (False)
    Not all forums are the same - seek and you shall find

  3. #3
    Registered User
    Join Date
    06-17-2008
    Posts
    2

    thanks

    Thanks for the help.
    I'll try that.

    Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1