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
"(E-Mail Removed)" 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.
>
|
Similar Threads
