VBA moving a worksheet to another Workbook

[KenY]

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(tab_name).Move
before:=Workbooks("BVInf_10x_consolidator.xls").Worksheets(Worksheets.Count)

Thanks

 

[Mike H]

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]

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

 

[kounoike]

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").Worksheets.Count)

keizi

 

[KenY]

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").Sheets("version")

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

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

Thanks

 

[kounoike]

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_name).Activate
and
Workbooks("BVInf_10x_consolidator.xls").Sheets("version").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

 

[Gizmo63]

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

 

[Mark Leonard]

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

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(tab_name).Move
before:=Workbooks("BVInf_10x_consolidator.xls").Worksheets(Worksheets.Count)

Thanks

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