Murray Brown
asked on
Excel VBA - copying whole sheet from another workbook
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
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
Does it hang and give you an error message?
ASKER
no error message - just data not being copied across. Thanks
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
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...
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
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.cop y
ThisWorkbook.Paste
Application.CutCopyMode = False
Dave
Should it not be
ThisWorkbook.Activate
Sheets("sheet1").cells.cop
ThisWorkbook.Paste
Application.CutCopyMode = False
Dave
@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)
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)
@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.
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.
The below code is corrected. I just tested with test data
dave
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
@Cdebel - agreed.
Please try cdebel's first.
otherwise, try mine.
Dave
Please try cdebel's first.
otherwise, try mine.
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes - Cdebels does work. Don't give me points. Use his solution
dave
dave
@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
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
ASKER
Hi. Thanks very much