Copying 1 worksheet to new workbook with data and formats only

[stac2410]

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

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

1.) Copy the Owner Report shee

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

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

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

5.) Minimizes the original workbook (but not closes

6.) Makes the new workbook "Owner Report - Stacie.xls" the activ
windo

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

Stacie

 

[Don Guillett Excel MVP]

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 itprompts
 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 nextstep is
what
 I'm needing help with.  I need a macro (that will be called from theprevious
 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 worksheetname
 "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."

 

[stac2410]

Don Guillett Excel MVP wrote on 07/17/2010 08:19 ET

On Jul 16, 11:51 am, stac2410 wrote
I would copy the sheet to a new book and then clean up whatever i
necessar

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

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