Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: simple copy and paste

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok. this is a stupid request.

    I have two workbooks.
    I want a macro to copy all the cells of worksheet1(wbook1) to worksheet2 in the second Wbook.

    what is the simplest way to do this using VBA code

    TIA

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    record it!

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    brett. the problem is that the record function records the final outcome.. not all the actions taken to achieve it.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here in agonizing detail are the actions you could take. This code is definitely the long way to the solution, but you did say you wanted to see "all the actions taken to achieve it."

    This code works just fine but it's darned inefficient. In real applications you could do the same thing with just a few lines of code. However, some new readers may find this code useful if they are getting accustomed to VBA, especially for the first time.

    Purists look the other way please.

    '''''''''''''''''''

    'Prepare Excel for the import:
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    'First, see if you have the Workbook1 file open; if not then open it:
    On Error GoTo b:
    Windows("Workbook1.xls").Activate
    'If you do not have the file open there will be an error and it will goto b:
    GoTo c:
    b:
    ChDir "C:YourFilePath"
    Workbooks.Open Filename:="C: YourFilePath Workbook1.xls"
    'This will open the Workbook1 file
    c:

    'Now when we get to c: the file is open either way so we activate it:
    Windows("Workbook1.xls").Activate
    'Then select your worksheet...
    Sheets("Sheet1").Select
    '...then select all the cells in the sheet...
    Cells.Select
    '...and then copy the entire sheet's range onto the clipboard.
    Selection.Copy

    'Next, re-activate the Workbook2 file:
    Windows("Workbook2.xls").Activate
    'Then select the destination worksheet...
    Sheets("Sheet2").Select
    '...then select the entire sheet's range...
    Cells.Select
    '...and Paste Special Values from Workbook1's Sheet1 into Workbook2’s Sheet2:
    Selection.PasteSpecial Paste:=xlValues
    ‘Go to A1, it seems like a nice place to set up shop
    Range("A1").Select
    ‘Clear the clipboard
    Application.CutCopyMode = False

    'Close Workbook1
    Windows("Workbook1.xls").Activate
    ActiveWindow.Close

    ‘Return Excel to its original condition
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

    ''''''''''''''''''''''''''''

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Couldn't resist, here's that code condensed into 3 lines, in case you wanted to keep it simple. The only thing is, it does not select all cells as you specified (your system would likely run out of memory if you tried A1:IV65536), but you probably don't have all 16,777,216 cells populated. Adjust the range as needed.

    Workbooks.Open Filename:="C:YourFilePathWorkbook1.xls"
    ThisWorkbook.Worksheets("Sheet2").Range("A1:Z50").Value = _
    Workbooks("Workbook1.xls").Worksheets("Sheet1").Range("A1:Z50").Value
    Workbooks("Workbook1.xls").Close

    HTH

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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