Copy Worksheet Function

[Adam.Sheehy]

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.

 

[Guest]

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