+ Reply to Thread
Results 1 to 14 of 14

Copying and pastiing cell values

  1. #1
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Question Copying and pastiing cell values

    Hi,

    I would like to request for some assistance in two tasks. First is to copy the cell values and formats from a workbook and secondly paste the cell values along with the formats into another workbook.

    For example the code should be able to copy the cell values from workbook C:\CALS\FIRST RUNNER.xlsm, the cell values are in the range AD2:AG16 of sheet1(2). And secondly the code
    should be able to paste those cell values along with the formats into Sheet1 of workbook C:\CALS\RUNNER.xlsm where the range for pasting the values and the formats is A2:D16.

    Thanks
    Last edited by LAVA2; 05-17-2020 at 11:56 AM.

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    Hi, you could use for example this code:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Copying and pastiing cell values

    Hi thanks for the reply, i missed to mention about pasting formats can u assist me in that too?

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    you can change xlPasteValues into xlPasteValuesAndNumberFormats
    Cheers
    Erwin

  5. #5
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Copying and pastiing cell values

    Again Thank you very much Erwin

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copying and pastiing cell values

    This should also work:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copying and pastiing cell values

    @ Erwin - I was working on the duplicate post and only posted so the time spent wouldn't be wasted.

    And, thanks for the rep!
    Last edited by xladept; 05-17-2020 at 07:39 PM.

  8. #8
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Copying and pastiing cell values

    Sub CopyPaste()
    Dim wsc As Worksheet, wst As Worksheet
    Dim wbc As Workbook, wbt As Workbook
    Set wbc = Workbooks.Open("C:\CALS\FIRST RUNNER.xlsm")
    Set wsc = Worksheets("sheet1(2)")
    Set wbt = Workbooks.Open("C:\CALS\RUNNER.xlsm")
    Set wst = Worksheets("Sheet1")
    wsc.Range("AD2:AG16").Copy
    wst.Range("A2").PasteSpecial xlPasteValues
    wbt.Save
    wbt.Close
    wbc.Close
    End Subthis code shows error pls help
    Last edited by LAVA2; 05-19-2020 at 04:18 PM.

  9. #9
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    Exactly where shows the error, at what command/line?
    And I see a difference in the original code at the "Set wst" line. In the original code this was:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  10. #10
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Copying and pastiing cell values

    the error is at :

    Set wsc = Worksheets("sheet1(2)")

  11. #11
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    Still an error if you do this:?
    Please Login or Register  to view this content.
    Please note the space between 1 and (
    Cheers
    Erwin

  12. #12
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Copying and pastiing cell values

    What if the workbook is already open can u suggest an edit in this one

    Sub CopyPaste()
    Dim wsc As Worksheet, wst As Worksheet
    Dim wbc As Workbook, wbt As Workbook
    Set wbc = Workbooks("C:\CALS\FIRST RUNNER.xlsm")
    Set wsc = Worksheets("sheet1 (2)")
    Set wbt = Workbooks("C:\CALS\FIRST RUNNER.xlsm")
    Set wst = Worksheets("Sheet1 (2)")
    wsc.Range("AD2:AG16").Copy
    wst.Range("A2").PasteSpecial xlPasteValues
    wbt.Save
    wbt.Close
    wbc.Close
    End Sub

  13. #13
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    It's very difficult to read this, please use code tags. (the hash-tag) in the edit-screen
    Cheers
    Erwin

  14. #14
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    660

    Re: Copying and pastiing cell values

    Please Login or Register  to view this content.

+ 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. Need help copying and pasting cell values on cell change
    By ghaulk in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-16-2013, 07:45 AM
  2. Replies: 8
    Last Post: 07-04-2013, 11:04 AM
  3. creating cell to cell formulas vs just copying values?
    By buckhunt122 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2011, 09:28 PM
  4. Copying cell values to different file
    By bhodge10 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-03-2011, 04:53 PM
  5. copying and pasting cell values
    By kurifodo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2011, 01:55 PM
  6. Copying Cell Values
    By crabtrees in forum Excel General
    Replies: 3
    Last Post: 08-17-2006, 04:15 PM
  7. [SOLVED] Copying values into another cell
    By Bman in forum Excel General
    Replies: 5
    Last Post: 01-09-2006, 03:58 PM
  8. Copying Values from a TextBox to a cell
    By MacroMike in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 07:06 AM

Tags for this Thread

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