[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Copy thru VBA

colleen1980@gmail.com

12/19/2006 9:19:00 PM

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.
3 Answers

JLGWhiz

12/19/2006 11:21:00 PM

0

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

JLGWhiz

12/20/2006 12:05:00 AM

0

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

mikeq

12/20/2006 3:26:00 AM

0

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