Copy thru VBA
 

Hi: I create a new workbook. How to i copy all the sheets from my old
workbook into my new work book. I mean when ever i open my new work
book all of the sheets no i am sorry from sheet1 to sheet10 will be
copy into my new work book will all of the formulas. I am sorry i ask
this question before but i need only to copy some of the sheets not all
of them.

Thanks,

Anna.

Copy thru VBA
 

The easy way is to SaveAs a different file name and then delete the sheets
you don't need.

If you want to copy only the formulas, without the data content in the
cells, then add the new workbook and use copy paste special xlFormulas
for each sheet you want to be in the new workbook. You can select the entire
sheet to copy by clicking in the cell to the left or column A and above row
1, then select A1 on the sheet that you want to paste to. For only ten
sheets, the copy and paste is also easy.

"Anna" wrote:

Hi: I create a new workbook. How to i copy all the sheets from my old
workbook into my new work book. I mean when ever i open my new work
book all of the sheets no i am sorry from sheet1 to sheet10 will be
copy into my new work book will all of the formulas. I am sorry i ask
this question before but i need only to copy some of the sheets not all
of them.

Thanks,

Anna.

Copy thru VBA
 

This is untested and probably needs a lot of touch up, but it gives you an
idea of what the VBA code would look like. Since there is no way to know if
the worksheets you want to copy are contiguous, this code assumes they are.


Sub CpyWkb()

Set NewBook = Workbooks.Add
With NewBook
.Title = "MyNewWkBk"
.Subject = "MySubj"
.SaveAs Filename:="MyFileName.xls"
End With
Counter = 1
Do While Counter < 8

Set newSheet = Sheets.Add(After:= Worksheets(GetLastSheet)Type:=xlWorksheet )

Loop

Set Wkb = Workbooks("MyOldWkBk.xls")
For i = 1 To 10
For each sheet in Wkb
Worksheets(i).Copy Workbooks("MyNewWkBk.xls")
After:=Worksheets"(GetLastSheet)
Next

End Sub


"Anna" wrote:

Hi: I create a new workbook. How to i copy all the sheets from my old
workbook into my new work book. I mean when ever i open my new work
book all of the sheets no i am sorry from sheet1 to sheet10 will be
copy into my new work book will all of the formulas. I am sorry i ask
this question before but i need only to copy some of the sheets not all
of them.

Thanks,

Anna.

Copy thru VBA
 

Open the two workbooks.
On the original workbook hold down the Ctrl Key & left click on the tabs you
want to copy/move
When you have them all Right click on one of them, select "Move or Copy",
select the "To book" and put a check mark in "Create a copy" if thats what
you want.
--
Mike Q.


"Anna" wrote:

Hi: I create a new workbook. How to i copy all the sheets from my old
workbook into my new work book. I mean when ever i open my new work
book all of the sheets no i am sorry from sheet1 to sheet10 will be
copy into my new work book will all of the formulas. I am sorry i ask
this question before but i need only to copy some of the sheets not all
of them.

Thanks,

Anna.

Copy thru VBA
 

Hi: i try to refine the code is this is correct. Also where i copy that
function in old or the new work book also how to i call that function?

Sub CpyWkb()
Dim NewBook as Workbook, Wkb as Workbook, wks as Worksheet
Set NewBook = Workbooks.Add
With NewBook
.Title = "MyNewWkBk"
.Subject = "MySubj"
.SaveAs Filename:="C:\webpages\MyFileName.xls" '<------- path
here
End With
Set Wkb = Workbooks("MyOldWkBk.xls")
For each wks in Wkb.worksheets
wks.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count )
Next
End Sub

Thanks,
Anna.

JLGWhiz wrote:
This is untested and probably needs a lot of touch up, but it gives you an
idea of what the VBA code would look like. Since there is no way to know if
the worksheets you want to copy are contiguous, this code assumes they are.


Sub CpyWkb()

Set NewBook = Workbooks.Add
With NewBook
.Title = "MyNewWkBk"
.Subject = "MySubj"
.SaveAs Filename:="MyFileName.xls"
End With
Counter = 1
Do While Counter < 8

Set newSheet = Sheets.Add(After:= Worksheets(GetLastSheet)Type:=xlWorksheet )

Loop

Set Wkb = Workbooks("MyOldWkBk.xls")
For i = 1 To 10
For each sheet in Wkb
Worksheets(i).Copy Workbooks("MyNewWkBk.xls")
After:=Worksheets"(GetLastSheet)
Next

End Sub


"Anna" wrote:

Hi: I create a new workbook. How to i copy all the sheets from my old
workbook into my new work book. I mean when ever i open my new work
book all of the sheets no i am sorry from sheet1 to sheet10 will be
copy into my new work book will all of the formulas. I am sorry i ask
this question before but i need only to copy some of the sheets not all
of them.

Thanks,

Anna.