Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Function
Hi guys!
I have some code to copy a certain worksheet within a workbook to another workbook: .... MYSHEET.Copy ActiveWorkbook.SaveAs strSaveName ActiveWorkbook.Close .... According to the help docs using copy with no "before" or "after" will copy the worksheet to a new workbook (which is what I want it to do). The problem is that if I don't protect the structure of the original workbook - let's call it "WorkBook A" - then "WorkBook A" remains active and saves itself as a new name (strSaveName). I stepped through the code slowly and noticed that WorkBook A just minimizes in Excel but still remains in the foreground and the new copy pops up but remains in the background... Is there a way around this so I don't have to protect the workbook... or some other code to do what I want? Hope this makes sense. Thanks for viewing. -Adam. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheet Function
When you copy a worksheet to another workbook, the workbook to which it is
copied becomes the active workbook. there is a good reason for this because you need to be able to identify it and you can do this wirh activeworkbook and assign it to a variable. The following code (untested) should help:- Dim MYSHEET As Worksheet Dim wbAct As Workbook Dim wbNew As Workbook Set wbAct = ActiveWorkbook Set MYSHEET = ActiveSheet MYSHEET.Copy Set wbNew = ActiveWorkbook wbAct.SaveAs strSaveName wbAct.Close -- Regards, OssieMac " wrote: Hi guys! I have some code to copy a certain worksheet within a workbook to another workbook: .... MYSHEET.Copy ActiveWorkbook.SaveAs strSaveName ActiveWorkbook.Close .... According to the help docs using copy with no "before" or "after" will copy the worksheet to a new workbook (which is what I want it to do). The problem is that if I don't protect the structure of the original workbook - let's call it "WorkBook A" - then "WorkBook A" remains active and saves itself as a new name (strSaveName). I stepped through the code slowly and noticed that WorkBook A just minimizes in Excel but still remains in the foreground and the new copy pops up but remains in the background... Is there a way around this so I don't have to protect the workbook... or some other code to do what I want? Hope this makes sense. Thanks for viewing. -Adam. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to copy the same image into worksheet with a function | Excel Worksheet Functions | |||
My copy worksheet function doesn't work | Excel Discussion (Misc queries) | |||
Is there a function to copy an entire worksheet? | Excel Worksheet Functions | |||
copy worksheet with formulas and vba function | Excel Programming | |||
formula/function to copy from worksheet to worksheet | Excel Programming |