VBA moving a worksheet to another Workbook
 

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

VBA moving a worksheet to another Workbook
 

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

VBA moving a worksheet to another Workbook
 

Thanks - seems I was using the wrong collection - Sheets does the trick
across Workbooks, but worksheets is ok within a workbook!


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

VBA moving a worksheet to another Workbook
 

"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

VBA moving a worksheet to another Workbook
 

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

VBA moving a worksheet to another Workbook
 

Sorry, but I have no idea.
By the way, Can you run code like this without error ?
Workbooks("BV_Analysis_array_4.xls").Sheets(tab_na me).Activate
and
Workbooks("BVInf_10x_consolidator.xls").Sheets("ve rsion").Activate

if not, maybe your two workbooks are not in the same Excel instance.
my code assumes two workbooks reside in the same instance of Excel.

keizi

"KenY" wrote in message
...
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

VBA moving a worksheet to another Workbook
 

You have 2 potential issues here that I have run into doing a similar task.
1. When you auto name a tab you have to be sure it doesn't exceed the 30 (I
think) character limit or duplicate one that already exists.
2. Excel gets into a mess if you try to move or copy too many sheets between
workbooks using macros and generally bugs out.

My solution has been to switch between the workbooks and use the
"worksheets.add" function in your destination workbook. I have not yet found
a limit doing it his way.

In this code I am copying pages from "Rangefile" into my "Templatefile".

'add sheet to template file and copy from current sheet in range plan
Workbooks(TemplateFile).Activate
Worksheets.Add
Range("a1").Select
ActiveSheet.Move after:=Worksheets(LastSheet)
Workbooks(RangeFile).Activate
Cells.Select
Selection.Copy
Workbooks(TemplateFile).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
'[add some code here to reformat the new tab]


Note that you have to add a sheet, go back and copy what you want to move
and then return to the destination to paste it; this may work with 'cut'
instead of 'copy' but I haven't tried it.

Later on I rename the new tab and update the "LastSheet" variable which
allows me to keep the sheets in order.
ActiveSheet.Name = [YourNewTabName]
LastSheet = ActiveSheet.Name

HTH

Giz

"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

VBA moving a worksheet to another Workbook
 

"Gizmo63" wrote:

You have 2 potential issues here that I have run into doing a similar task.
1. When you auto name a tab you have to be sure it doesn't exceed the 30 (I
think) character limit or duplicate one that already exists.
2. Excel gets into a mess if you try to move or copy too many sheets between
workbooks using macros and generally bugs out.

My solution has been to switch between the workbooks and use the
"worksheets.add" function in your destination workbook. I have not yet found
a limit doing it his way.

In this code I am copying pages from "Rangefile" into my "Templatefile".

'add sheet to template file and copy from current sheet in range plan
Workbooks(TemplateFile).Activate
Worksheets.Add
Range("a1").Select
ActiveSheet.Move after:=Worksheets(LastSheet)
Workbooks(RangeFile).Activate
Cells.Select
Selection.Copy
Workbooks(TemplateFile).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
'[add some code here to reformat the new tab]


Note that you have to add a sheet, go back and copy what you want to move
and then return to the destination to paste it; this may work with 'cut'
instead of 'copy' but I haven't tried it.

Later on I rename the new tab and update the "LastSheet" variable which
allows me to keep the sheets in order.
ActiveSheet.Name = [YourNewTabName]
LastSheet = ActiveSheet.Name

HTH

Giz

"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

Did this solve the issue? I hit a move problem when I Upgraded to Excel
2007, what version of Excel are you on?