VBA Excel 2010 - Move sheet to another workbook

csehz
csehz used Ask the Experts™
on
Dear Experts,

I would like to move a sheet from workbook to workbook with VBA code, but with that condition that sheet and workbook names should not be defined, as those are always different.

So assuming that only two workbooks are open at the code running moment, could you please advise how to move the active workbook first sheet, to the second workbook as last sheet.

As I can imagine the soluton would be around using pattern Worksheets(1)

thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2010
Top Expert 2013
Commented:
This will work
Dim destBook As Workbook
If Workbooks(1).Name = ActiveWorkbook.Name Then
  Set destBook = Workbooks(2)
Else
  Set destBook = Workbooks(1)
End If
ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)

Open in new window

You'll want to add some error handling if this is for a client.
csehzIT consultant

Author

Commented:
Thanks very much, I have tested the macro as it is in the attached code, but got an error message

Run-time error 1004
Copy method of Worksheet class failed

Do you have maybe idea why, basically at the last row

ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)
Sub TestMoveSheet()

Dim destBook As Workbook
If Workbooks(1).Name = ActiveWorkbook.Name Then
  Set destBook = Workbooks(2)
Else
  Set destBook = Workbooks(1)
End If
ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)

End Sub

Open in new window

Awarded 2010
Top Expert 2013

Commented:
Are they in different instances of Excel or the same one? (Different gives me out of bounds error which is expected).
How exactly do you have it set up? I can't replicate the error.
I just created two new workbooks and ran the code both in a module and in one of the sheets. You're not calling that code from an event or in a loop or anything are you?
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

csehzIT consultant

Author

Commented:
I have tested like this

1) start Excel 2010
2) opening a new workbook and so this is Book1
3) opening another workbook and so this is Book2
4) for Book2 in the module section I have copied the code and run

Can it be maybe that in Tools/References missing something on my computer?

thanks,
Awarded 2010
Top Expert 2013

Commented:
There is nothing in the references that would cause that error if it was missing that I know of. I'm using 2003 and 2007 and it's working just fine using the same steps. You could try it like this and see what happens
Sub TestMoveSheet()

Dim destBook As Workbook
If Workbooks(1).Name = ActiveWorkbook.Name Then
  Set destBook = Workbooks(2)
Else
  Set destBook = Workbooks(1)
End If
ActiveWorkbook.Worksheets(1).Copy After:=destBook.Worksheets(destBook.Worksheets.Count)

End Sub

Open in new window

I'll see if any of the other Experts can shed any light on this issue.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you have a personal macro workbook?
Most Valuable Expert 2011
Top Expert 2011

Commented:
Better to specify the target workbook by name:
Sub TestMoveSheet()

Dim destBook As Workbook
  Set destBook = Workbooks("Book1")
ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)

End Sub

Open in new window


for example.
Awarded 2010
Top Expert 2013

Commented:
rorya, you missed the above. Asker does not want to hard code the name.
Try

Sub testmovesheet()
For Each wb In Application.Workbooks
If wb.Name = ActiveWorkbook.Name Then Set swb = wb Else Set twb = wb
Next wb
swb.ActiveSheet.Copy , twb.Worksheets(twb.Worksheets.Count)
End Sub
Most Valuable Expert 2011
Top Expert 2011

Commented:
Then the code needs some way of determining which book is which! :) Otherwise who knows where it will end up...
There are only two workbooks and the active one is the source.
Most Valuable Expert 2011
Top Expert 2011
Commented:
If you know there will be only two visible ones for example:
Sub TestMoveSheet()

Dim destBook As Workbook
Dim wbk As Workbook
Dim sourcebook As Workbook
Set sourcebook = ActiveWorkbook
For Each wbk In Application.Workbooks
    If wbk.Windows(1).Visible And Not wbk Is sourcebook Then
        Set destBook = wbk
        Exit For
    End If
Next wbk
If Not destBook Is Nothing Then
    sourcebook.Worksheets(1).Copy After:=destBook.Worksheets(destBook.Worksheets.Count)
End If

End Sub

Open in new window

csehz, can you try if the copy action works on workbooks that do not have named references ?

When you get the same error at least this rules out

http://support.microsoft.com/kb/210684/en-us
Awarded 2010
Top Expert 2013

Commented:
akoster, he started two fresh, new workbooks (no names defined) and still got the issue. You must have missed that above.
Those new workbooks are started from a template. If the used template contains names, than those names will be used in the fresh workbooks as well.
Awarded 2010
Top Expert 2013
Commented:
Good point. That could be. Also, I would suggest disabling any add-ons just to make sure they aren't interfering so we can narrow down the issue (if the above doesn't fix it of course).
csehzIT consultant

Author

Commented:
Dear All, sorry for the late answer and thanks for the efforts spent on my case.

Tommy, I have tried the code which you copied at comment 16/05/11 03:21 PM, ID: 35769159, somehow for me it brings the same message like the first. I assume so maybe some version problem could be.

Rorya and Ssaqibh, your codes are working for me and my target is completed with them. That any kind of two workbook is opened, sheet is moved to the second.
csehzIT consultant

Author

Commented:
I would feel fair to split the points between three of you, for sure also Tommy's one is working just maybe I am doing something wrong or version difference

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial