Solved

Excel VBA - copying whole sheet from another workbook

Posted on 2011-02-27
13
712 Views
Last Modified: 2012-05-11
Hi

I am using the code shown in the code section below to copy all the data
from a sheet in another workbook. The data doesn't copy over for some reason
Sub Main()

    Dim MYDOC_DIR As String
  
    On Error GoTo EH
    
    MYDOC_DIR = Environ("userprofile") & "\Desktop"
    Dim oWb As Excel.Workbook
    Set oWb = Application.Workbooks.Open(MYDOC_DIR & "\" & "FILE FROM ITS.xlsm")
   
    oWb.Sheets(1).Cells.Copy
    
    Sheets("Sheet1").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    oWb.Close (False)
    Set oWb = Nothing
    
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Open in new window

0
Comment
Question by:murbro
  • 6
  • 5
  • 2
13 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 34992926
Does it hang and give you an error message?
0
 

Author Comment

by:murbro
ID: 34992949
no error message - just data not being copied across. Thanks
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34992958
I think the error come from that once you open the workbook, your initial workbook is not the activeworkbook anymore... (so from line 13 to 16, you should refer to ThisWorkbook
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 10

Expert Comment

by:cdebel
ID: 34992963
There...    Before line 13, just do ThisWorkbook.Activate.

It will select your initial workbook, then your "Select" on line 13 will apply to this workbook, and not the one that you have just opened...

    ThisWorkbook.Activate
    Sheets("Sheet1").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 34993039
cdebel - I don't believe your wode will work either - after the Sheets("Sheet1").select, Sheets("Sheet1") becomes the ActiveSheet does it not?

Should it not be

ThisWorkbook.Activate
Sheets("sheet1").cells.copy
ThisWorkbook.Paste
Application.CutCopyMode = False


Dave
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34993050
@dlmile:
Yes it become active sheet.
What he seems to want is to copy data from Sheet1 (in oWB) into ThisWorkbook.Sheets(1)...
So my code does work.

Yours doesn't work... (i'm refering to this line: ThisWorkbook.Paste)

0
 
LVL 10

Expert Comment

by:cdebel
ID: 34993060
@dlmile: also, in your solution, you seems to copy data FROM THISWORKBOOK, while it should be copied TO THISWORKBOOK.

At least, this is what his code says there: oWb.Sheets(1).Cells.Copy

So we should let him see if i got it wrong.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34993061
The below code is corrected.  I just tested with test data

dave
Sub Main()
Dim myCurrent As Workbook

    Dim MYDOC_DIR As String
  
    On Error GoTo EH
    
    Set myCurrent = ActiveWorkbook
    
    MYDOC_DIR = Environ("userprofile") & "\Desktop"
    Dim oWb As Excel.Workbook
    Set oWb = Application.Workbooks.Open(MYDOC_DIR & "\" & "FILE FROM ITS.xlsm")
   
    oWb.Sheets(1).Cells.Copy
    
    myCurrent.Activate
    Sheets("Sheet1").Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    oWb.Close (False)
    Set oWb = Nothing
    
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 34993070
@Cdebel - agreed.

Please try cdebel's first.  

otherwise, try mine.

Dave
0
 
LVL 10

Accepted Solution

by:
cdebel earned 500 total points
ID: 34993072
@dlmile:
So basically, this is exactly what i did previously.

The only thing you add is an unnecessary line (line 8)... because ThisWorkbook always refer to ThisWorkbook (where the VBA code is).  So there's no need to remember which workbook it is to activate it back later...

Let's hope that the askers will notice that i gave him his answer.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34993073
Yes - Cdebels does work.  Don't give me points.  Use his solution

dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34993085
@Cdebel - sorry about that.  I almost never use ThisWorkbook because I didn't realize the truth to your comment (This Workbook will allways be where the source code is).  Particularly, because most of my stuff ends up in addins :)

Also, in my original comment, I didn't snap to the realization the COPY had already been done before your code (your snippet was just short enough for me to miss that, for some reason) - otherwise I wouldn't have posted.


Thanks for the lesson!

Dave
0
 

Author Closing Comment

by:murbro
ID: 34996030
Hi. Thanks very much
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 77
Best Excel  formula for  this scenario 2 37
Excel formula Sumif not working 4 28
Left trim cells in column A Excel vba 2 31
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question