Replicating a Spreadsheet into a new Workbook

Bright01
Bright01 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why don't you Copy all values of survey sheet and then past special and select Values on the Paste special dialog?
kgerbChief Engineer

Commented:
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

Author

Commented:
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.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

kgerbChief Engineer

Commented:
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
Most Valuable Expert 2012
Top Expert 2012
Commented:
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

Author

Commented:
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.
Chief Engineer
Commented:
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

Author

Commented:
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.
kgerbChief Engineer

Commented:
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

Author

Commented:
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.
kgerbChief Engineer
Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
kgerbChief Engineer

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

Kyle

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial