You have a point that sheets is not the complete answer - I jumped to
conclusions after recording a macro and finding Sheets in it as well as in
the reply from MikeH.

Unfortunately, your proposal does not work either.

I even got the stage of directly referring to a known sheet within the
BVInf_10x_consolidator.xls file and it still does not work.

This is what I tried also
Sheets(tab_name).Move
after:=Workbooks("BVInf_10x_consolidator.xls").She ets("version")

Even using the more explicit
Workbooks("BV_Analysis_array_4.xls").Sheets(tab_na me).Move
after:=Workbooks("BVInf_10x_consolidator.xls").She ets("version")
code does not work.

any other ideas you have would be most welcome - this one is really annoying
me.

Thanks





--
KenY


"kounoike" wrote:

"KenY" wrote in message
...
Thanks - seems I was using the wrong collection - Sheets does the trick
across Workbooks, but worksheets is ok within a workbook!

I don't think Sheets does the trick. My guess is that your Worksheets.Count
counts worksheets of your workbook your macro reside, not worksheets of
Workbooks("BVInf_10x_consolidator.xls").

I'm not sure, but i think
Workbooks("BVInf_10x_consolidator.xls").Worksheets (Worksheets.Count) shoud
be
Workbooks("BVInf_10x_consolidator.xls").Worksheets (Workbooks("BVInf_10x_consolidator.xls").Worksheet s.Count)

keizi


--
KenY


"Mike H" wrote:

Try:-

Sub surface()
Workbooks("Book1").Sheets("Sheet2").Move
After:=Workbooks("OverTime.xls").Sheets(3)
End Sub

Change workbook and sheet names to suit, these just happened to be 2 that
I
had open.

Mike

"KenY" wrote:

Using a macro, I can easily move a worksheet within a workbook using
the line

worksheets("KY").move after:=worksheets(worksheets.count)

However, when I try to move the sheet to another workbook using the
line
below (where tab_name is a text variable), I get a subscript out of
range
error. Can anyone spot my silly mistake?

Workbooks("BV_Analysis_array_4.xls").Worksheets(ta b_name).Move
befo=Workbooks("BVInf_10x_consolidator.xls").Wo rksheets(Worksheets.Count)

Thanks
--
KenY