Solved

Replicating a Spreadsheet into a new Workbook

Posted on 2012-03-22
14
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 42

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
 

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 42

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

624 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