Solved

Replicating a Spreadsheet into a new Workbook

Posted on 2012-03-22
14
386 Views
Last Modified: 2012-03-23
EE Professionals,

I have a Spreadsheet that represents a "Survey" that is part of a bigger WB.  I'm looking for a simple Macro that will take the specific (Hidden) Worksheet representing the Survey, and output it with "no formulas" as an XLS WB/WS with a predetermined name identified withinn the Macro. In other words, upon triggering the Macro in the bigger WB, another Excel WB is created and a particular Worksheet, within the bigger WB, is copied and pasted to the new Workbook as Data only (no formulas).  I'm happy to make it a range name if that is easier.  The predetermined name of the WB and WS (separate names) should be in the Macro so that it can be easily modified.  Finally, no other tabs should exist in the new WB.

That's it!

Thank you in advance,

B.
0
Comment
Question by:Bright01
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 37754593
Why don't you Copy all values of survey sheet and then past special and select Values on the Paste special dialog?
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37754628
Hello Bright01,
Try this.  I think it will work for you.
Sub ExportSheetOnlyValues()
Dim sWBName As String, sWSName As String
sWBName = "NewWorkbook" 'Add path if you wish (e.g. C:\My folder\NewWorkbook)
sWSName = "NewWorksheet"
Sheets("Sheet1").Copy
With ActiveSheet
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues
    .Name = sWSName
    .Range("A1").Select
    Application.CutCopyMode = False
End With
ActiveWorkbook.SaveAs sWBName
ThisWorkbook.Activate
End Sub

Open in new window

Kyle
0
 

Author Comment

by:Bright01
ID: 37754858
Kyle,

Thanks for the code.  Here's what happens.  It makes the copy and then gets caught in some form of loop.  Can you add some error checking?  Perhaps it doesn't know where to put the new file....... how would you direct it to the desktop?

Help,

B.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37755386
hmmm...I don't think it's getting caught in a loop since there isn't a loop.  Is it throwing an error?  If so, what line is it stopping on?

If you want the new file saved to the desktop then add the path on line 3 inside the quotes right before NewWorkbookName.  For example, if I wanted the file on my desktop I would change line 3 to be

sWBName = "C:\Users\Kyle\Desktop\NewWorkbook"

It will then save the workbook to the desktop and give it the name NewWorkbook.

Let me know how that works.

Kyle
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 100 total points
ID: 37755916
The problem may be with range names.  Are you getting prompted on that?  Here's a related article:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_8736-Improved-Move-Copy-Add-in-Replacement.html

Please advise if you're seeing the error message loop as discussed in this article.

If it is, then add:

Application.DisplayAlerts = False before line 5

Then at the end, add:

Application.DisplayAlerts = TRUE before end sub

--------------------------------------------------------------------------------------------
Also, for less bugs if you're deploying,  for your desktop path use:

ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlNormal

on, lol

Dave
0
 

Author Comment

by:Bright01
ID: 37756573
Dave/Kyle,

OK Guys; I think I've got this. Here's the issue.  The WS I'm targeting to copy is;

1.) partially "protected" (certain cells cannot be changed)

and

2.) Is "hidden".

When I unhide and unprotect the sheet...... the macro works.   What additional lines can I add to deal with the fact it's hidden and partially locked?

Thank you,

B.
0
 
LVL 12

Accepted Solution

by:
kgerb earned 400 total points
ID: 37756857
Ok, here's a new one.  I think I was able to recreate the error.  My code would hang on the paste special line if the worksheet was protected.  Not exactly sure, but I think it had something to do with copying all cells and then pasting them back into ranges that were protected from being changed (just like you said).  Try it and see if it works for you.

I have also included Daves suggestion on getting the path to the desktop.  Thanks Dave!
Sub ExportSheetOnlyValues()
Dim sWBName As String, sWSName As String, bViz As Boolean, bProtect As Boolean
sWBName = "NewWorkbook" 'Add path if you wish (e.g. C:\My folder\NewWorkbook)
sWSName = "NewWorksheet"
With Sheets("Sheet1")
    bViz = .Visible
    .Visible = True
    .Copy
    .Visible = bViz
End With
With ActiveSheet
    bProtect = .ProtectContents
    .Unprotect
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues
    .Name = sWSName
    .Range("A1").Select
    Application.CutCopyMode = False
    If bProtect Then .Protect
End With
ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlNormal
ThisWorkbook.Activate
End Sub

Open in new window

Kyle
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Bright01
ID: 37758264
Kyle and Dave,

This works great!!!!  One question;

how do I control the replicated XLS to be an XLSM sheet?  I get a lot of compatability/range questions due to the fact that the Primary WB is an XLSM file and the one that shows up on the Desktop is an XLS sheet.

Thank you,

b.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37758333
Where did you put the code?  It should go into a normal module in the master workbook.  Assuming there is no code in the worksheet code pane, excel should have no problem saving it as a normal workbook after it is copied (i.e. you shouldn't be getting warnings).

If you have code in the worksheet code pane and want that removed upon export we can do that too.  Let us know.

Kyle
0
 

Author Comment

by:Bright01
ID: 37758390
Kyle,

The code is in a normal module in the master WB.  There is no code in the WS that's being copied/replicated.  For some reason when I fire the Macro, I get the correct new file but "the Compatability Checker" comes up.  I can then accept all of the changes but what it's doing is saving it on the desktop as a XLS file.  So I thought there may be a word or line that forces a XLSM copy instead.  That way there would be no incompatability.

Thanks,

B.
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 400 total points
ID: 37758455
Ok, well, I don't know what the problem is but I guess I can try to fix it :-)

In line 21 of the macro replace

xlNormal

with either of the following

xlOpenXMLWorkbook
xlOpenXMLWorkbookMacroEnabled

The first will force the workbook to be saved as an Excel 2007/2010 XLSX format.  The second will force a XLSM format.  See if that works for you.

Kyle
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37758472
I'm betting that is the problem and the fix.  

When the COPY was done, the default xlNormal SAVE is to .XLS the way Bright's save options must be set to default to .XLS (Bright that's Excel Options->Save).

Dave
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37758500
Thanks Dave, I never made the correlation between xlNormal and the "Save files in this fomat" combo in the options.  Good to know.

Kyle
0
 

Author Closing Comment

by:Bright01
ID: 37758699
Thanks guys....great job.  It works "almost" perfectly!  When I open the new WB, I get a link update to the original WB......aaaagggghhhh...... those are nightmares for me generally.  I'm going to try to fix it (ref. show formulas, check rangenames, etc. etc.).  If not, I'll post another question.

Again, much thanks....way cool!

B.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now