Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel |