<div>
Thanks very much, I have tested the macro as it is in the attached code, but got an error message<br />
<br />
Run-time error 1004<br />
Copy method of Worksheet class failed<br />
<br />
Do you have maybe idea why, basically at the last row<br />
<br />
ActiveWorkbook.Worksheets(<wbr />1).Copy , destBook.Worksheets(destBo<wbr />ok.Workshe<wbr />ets.Count)<wbr /><br />

<pre><code class="code-10 nolinks" id="code-8168084"><span>Sub TestMoveSheet()</span>
<span></span>
<span>Dim destBook As Workbook</span>
<span>If Workbooks(1).Name = ActiveWorkbook.Name Then</span>
<span>  Set destBook = Workbooks(2)</span>
<span>Else</span>
<span>  Set destBook = Workbooks(1)</span>
<span>End If</span>
<span>ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)</span>
<span></span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Are they in different instances of Excel or the same one? (Different gives me out of bounds error which is expected).<br />
How exactly do you have it set up? I can't replicate the error.<br />
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?
</div>


<div>
I have tested like this<br />
<br />
1) start Excel 2010<br />
2) opening a new workbook and so this is Book1<br />
3) opening another workbook and so this is Book2<br />
4) for Book2 in the module section I have copied the code and run<br />
<br />
Can it be maybe that in Tools/References missing something on my computer?<br />
<br />
thanks,
</div>


<div>
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<br />

<pre><code class="code-10 nolinks" id="code-20-35769159-1"><span>Sub TestMoveSheet()</span>
<span></span>
<span>Dim destBook As Workbook</span>
<span>If Workbooks(1).Name = ActiveWorkbook.Name Then</span>
<span>  Set destBook = Workbooks(2)</span>
<span>Else</span>
<span>  Set destBook = Workbooks(1)</span>
<span>End If</span>
<span>ActiveWorkbook.Worksheets(1).Copy After:=destBook.Worksheets(destBook.Worksheets.Count)</span>
<span></span>
<span>End Sub</span>
</code></pre>
I'll see if any of the other Experts can shed any light on this issue.
</div>


<div>
Do you have a personal macro workbook?
</div>


<div>
Better to specify the target workbook by name:<br />

<pre><code class="code-1 nolinks" id="code-20-35769313-1"><span>Sub TestMoveSheet()</span>
<span></span>
<span>Dim destBook As Workbook</span>
<span>  Set destBook = Workbooks(&quot;Book1&quot;)</span>
<span>ActiveWorkbook.Worksheets(1).Copy , destBook.Worksheets(destBook.Worksheets.Count)</span>
<span></span>
<span>End Sub</span>
</code></pre>
<br />
for example.<br />

</div>


<div>
rorya, you missed the above. Asker does not want to hard code the name.
</div>


<div>
Then the code needs some way of determining which book is which! :) Otherwise who knows where it will end up...
</div>


<div>
There are only two workbooks and the active one is the source.
</div>


<div>
csehz, can you try if the copy action works on workbooks that do not have named references ?<br />
<br />
When you get the same error at least this rules out <br />
<br />
<a href="http://support.microsoft.com/kb/210684/en-us" rel="ugc nofollow">http://support.microsoft.com/kb/210684/en-us</a>
</div>


<div>
akoster, he started two fresh, new workbooks (no names defined) and still got the issue. You must have missed that above.
</div>


<div>
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.<br />

</div>


<div>
Dear All, sorry for the late answer and thanks for the efforts spent on my case.<br />
<br />
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.<br />
<br />
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.
</div>


<div>
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
</div>


<div>
<div class="content wysiwyg-content">
Dear Experts,<br />
<br />
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.<br />
<br />
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.<br />
<br />
As I can imagine the soluton would be around using pattern Worksheets(1)<br />
<br />
thanks,
</div>
</div>


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,

VBA Excel 2010 - Move sheet to another workbook

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.