# Solved: Excel VBA to move worksheet to new excel file



## gjrobbins (Jan 25, 2010)

Hi

I am looking to create some code to move a sheet from one file to a new excel file. SO the code needs to open a new excel workbook and place the required sheet in the resultant new file.

I have managed to do this with an existing file "Moved Pivot" per the code below. This is ok but other people will be using my sheet so the location needs to be chosen by them. A new file seems to be the only solution?

_Workbooks.Open Filename:="C:\desktop\Optegra Installer\Moved Pivot.xls"

Dim oTargetSheet As Worksheet

Set oTargetSheet = Workbooks("Moved Pivot.xls").Worksheets(1)
Call ThisWorkbook.Worksheets("PIVOT_TB").Copy(oTargetSheet)_
_ Set oTargetSheet = Nothing
_

Any help much appreciated. Thanks

Jeremy


----------



## bomb #21 (Jul 1, 2005)

I don't understand the problem. To move (i.e. _re_move) a sheet from an open workbook to a fresh one is:

*Sheets("Sheet1").Move*

, for example.

"other people will be using my sheet so the location needs to be chosen by them"



Which wb is the code in?


----------



## gjrobbins (Jan 25, 2010)

Hi bomb#21

Sorry for the confusion..

I have one file which connects over ODBC and creates a pivot table of the data collected. The sheet name in this file is "Pivot_TB" This file has all the code in.

I am looking to copy this "Pivot_TB" sheet to a completely new workbook.

So the code I am looking to create will copy "Pivot_TB" and move it to a new blank worksheet which the user then can 'save as' to whatever location they want.

I have managed to be able to move this to an existing file called "Moved Pivot.xls"


DOes that help?

Thanks

Jeremy


----------



## bomb #21 (Jul 1, 2005)

I think it helps _me_, therefore it does. 

Assuming the "one file which connects over ODBC" is active at the time, then:

ActiveWorkbook.Sheets("Pivot_TB").*Move*

Unless I'm _still_ missing something. HTH


----------



## gjrobbins (Jan 25, 2010)

Unless I am missing something, this wont put it into a new workbook?

The code needs to effectively do the following (using excel menus..)

File - New - Blank Workbook

And deposit the "Pivot_TB" Worksheet in the new file.

The user then can select save as and put it wherever they desire

THanks

Is this possible?


----------



## bomb #21 (Jul 1, 2005)

Attached has 3 sheets -- 1, 2, Pivot_TB.

Run *test* (ActiveWorkbook.Sheets("Pivot_TB").Move) and you get (i) a new book containing Pivot_TB (ii) the original wb "behind" with Pivot_TB gone.

No "File - New - Blank Workbook" required, and "user then can select save as and put it wherever they desire".


----------



## gjrobbins (Jan 25, 2010)

Hi bomb#21

Thank you so much for the attachment, I changed the .move to .copy as I need to retain the original "Pivot_TB" sheet in the first file.

It works perfectly and was so much simpler than code obtained from so called VBA websites which I could not get to work.. so thanks once again

Jeremy

:up::up:


----------

