+ Reply to Thread
Results 1 to 8 of 8

Code to Copy and Paste

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Code to Copy and Paste

    Good morning, Gurus.

    I know I've seen the proper way to do this, but couldn't find it in my search. How would I properly code the following to copy and paste values from onw workbook to another?
        wbMyWb.Activate
        Range("D2").Copy
        ThisWorkbook.Activate
        Range("K1").Select
        ActiveSheet.Paste
        wbMyWb.Activate
        Range("E2").Copy
        ThisWorkbook.Activate
        Range("L1").Select
        ActiveSheet.Paste
        wbMyWb.Activate
        Range("G2").Copy
        ThisWorkbook.Activate
        Range("G1").Select
        ActiveSheet.Paste
    Thanks in advance.
    Last edited by [email protected]; 10-09-2008 at 02:06 PM.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573
    Sub Copy()
      Dim wbMyWb As Workbook
      Set wbMyWb = Workbooks("Sample.xls")
      wbMyWb.Worksheets("Sheet1").Range("A1").Copy ThisWorkbook.Worksheets("Sheet2").Range("A1")
    End Sub

  3. #3
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: Code to Copy and Paste

    You can do without switching from WB1 to WB2
    Sub copy1()
      Dim WsS As Worksheet, WsT As Worksheet
      Dim SourceRng As Range
      Dim PasteRng As Range
      Dim Counter As Long
      Dim Ccell As Range
      Dim MyArray
    
      Set WsS = Workbooks("Book1.xls").Worksheets("Sheet1") 'Needs to be open Open
      Set WsT = Workbooks("Book2.xls").Worksheets("Sheet1") 'Needs to be open Open
      Set SourceRng = Union(WsS.Range("D2"), WsS.Range("E2"), WsS.Range("G2"))
      Set PasteRng = Union(WsT.Range("K1"), WsT.Range("L1"), WsT.Range("G1"))
      
      ReDim MyArray(1 To SourceRng.Cells.Count)
      Counter = 1
      For Each Ccell In SourceRng
        MyArray(Counter) = Ccell
        Counter = Counter + 1
      Next
     Counter = 1
      For Each Ccell In PasteRng
        Ccell = MyArray(Counter)
        Counter = Counter + 1
      Next
    End Sub
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Try this
    Option Explicit
    Sub Copy()
        Dim wbMyWb As Workbook
        Dim tWbk   As Workbook
        Set wbMyWb = Workbooks("Test.xls")
        Set tWbk = ThisWorkbook
        With wbMyWb
            tWbk.Range("K1") = .Range("D2")
            tWbk.Range("L1") = .Range("E2")
            tWbk.Range("G1") = .Range("G2")
        End With
    End Sub
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706
    Ken - yours looked a lot neater, and avoided jumping between sheets.
    RW - your avoided jumping, but was longer than the original, and difficult to follow.
    Roy - As usual, comes to the rescue!

    One last question. If I just wanted to copy the values over, would it be as simple as shown below? (It's never as simple as it should be.)

    Option Explicit
    Sub Copy()
        Dim wbMyWb As Workbook
        Dim tWbk   As Workbook
        Set wbMyWb = Workbooks("Test.xls")
        Set tWbk = ThisWorkbook
        With wbMyWb
            tWbk.Range("K1") = .Range("D2").Value
            tWbk.Range("L1") = .Range("E2")
            tWbk.Range("G1") = .Range("G2")
        End With
    End Sub

  6. #6
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573
    Value is the default property of Range.

    While we could do Range("A1")=2, we should be more explicit and do: Range("A1").Value=2.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I was swapping backwards & forwards to check which cells you were copying & didn't notice I hadn't added the .Value. As Ken says I would usually always do so.

  8. #8
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706
    Thanks, guys. Have a good one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. code required to copy and paste rows matching a criteria.
    By Duguid1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-30-2008, 04:49 AM
  2. Code to Copy Data From one Worksheet to Another
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-04-2008, 01:00 AM
  3. Code to Copy Data From one Worksheet to Another
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2008, 09:47 PM
  4. VBA Code needed to copy and paste the format
    By deepikabalaji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2007, 05:15 AM
  5. Making Copy and Paste Code more efficient
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2006, 11:31 AM

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