Copying 1 worksheet to new workbook with data and formats only
 

Good afternoon! I've searched around for an answer for this and can't seem to
find either a situation similar enough to my own, or a solution that also
works
for me.

What I have is a workbook that has several sheets. I have a sheet called
"Reports" that has a button that is linked to a macro and it prompts
the user for input (such as a name, Stacie), then performs an autofilter on
sheet 1 for Stacie, then copies and pastes the data into another worksheet
"Owner Report". That part all works perfect. The next step is
what
I'm needing help with. I need a macro (that will be called from the previous
macro) that will do the following:

1.) Copy the Owner Report sheet

2.) Paste it into a new workbook with just values and formats (no formulas or
links to other workbooks)

3.) Saves the new workbook on the users desktop using the worksheet name
"Owner Report" then the criteria that was input - for example, this
one would be called "Owner Report - Stacie.xls"

4.) The original workbook makes the "Reports" sheet the active tab

5.) Minimizes the original workbook (but not closes)

6.) Makes the new workbook "Owner Report - Stacie.xls" the active
window

I've been trying to figure this out for days now with no solution yet, so any
help at all is GREATLY appreciated, thanks so much!!

Stacie

Copying 1 worksheet to new workbook with data and formats only
 

On Jul 16, 11:51*am, stac2410 wrote:
Good afternoon! I've searched around for an answer for this and can't seem to
*find either a situation similar enough to my own, or a solution that also
works
*for me.

*What I have is a workbook that has several sheets. *I have a sheet called
*"Reports" that has a button that is linked to a macro and it prompts
*the user for input (such as a name, Stacie), then performs an autofilter on
*sheet 1 for Stacie, then copies and pastes the data into another worksheet
*"Owner Report". * That part all works perfect. *The next step is
what
*I'm needing help with. *I need a macro (that will be called from the previous
*macro) that will do the following:

*1.) *Copy the Owner Report sheet

*2.) *Paste it into a new workbook with just values and formats (no formulas or
*links to other workbooks)

*3.) *Saves the new workbook on the users desktop using the worksheet name
*"Owner Report" then the criteria that was input - for example, this
*one would be called "Owner Report - Stacie.xls"

*4.) The original workbook makes the "Reports" sheet the active tab

*5.) *Minimizes the original workbook (but not closes)

*6.) *Makes the new workbook "Owner Report - Stacie.xls" the active
*window

*I've been trying to figure this out for days now with no solution yet, so any
*help at all is GREATLY appreciated, thanks so much!!

*Stacie
I would copy the sheet to a new book and then clean up whatever is
necessary

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Copying 1 worksheet to new workbook with data and formats only
 

Don Guillett Excel MVP wrote on 07/17/2010 08:19 ET :
On Jul 16, 11:51 am, stac2410 wrote:
Good afternoon! I've searched around for an answer for this and can't seem
to
 find either a situation similar enough to my own, or a solution that
also
works
 for me.

 What I have is a workbook that has several sheets.  I have a
sheet
called
 "Reports" that has a button that is linked to a
macro
and it prompts
 the user for input (such as a name, Stacie), then performs an
autofilter
on
 sheet 1 for Stacie, then copies and pastes the data into another
worksheet
 "Owner Report".   That part all works perfect.
 The next step is
what
 I'm needing help with.  I need a macro (that will be called from
the previous
 macro) that will do the following:

 1.)  Copy the Owner Report sheet

 2.)  Paste it into a new workbook with just values and formats
(no
formulas or
 links to other workbooks)

 3.)  Saves the new workbook on the users desktop using the
worksheet name
 "Owner Report" then the criteria that was input -
for
example, this
 one would be called "Owner Report - Stacie.xls"

 4.) The original workbook makes the "Reports" sheet
the active tab

 5.)  Minimizes the original workbook (but not closes)

 6.)  Makes the new workbook "Owner Report -
Stacie.xls" the active
 window

 I've been trying to figure this out for days now with no solution yet,
so any
 help at all is GREATLY appreciated, thanks so much!!

 Stacie


I would copy the sheet to a new book and then clean up whatever is
necessary

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Thanks for the help Don. I'll use your suggestion and see if I can give it
another go from that angle. If I'm unsuccessful, I'll send to you along with
the
information you require. Thanks again!