Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Replicating a Spreadsheet into a new Workbook

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
Bright01
Asked:
Bright01
  • 6
  • 5
  • 2
  • +1
3 Solutions
 
petr_hlucinCommented:
Why don't you Copy all values of survey sheet and then past special and select Values on the Paste special dialog?
0
 
kgerbChief EngineerCommented:
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
 
Bright01Author 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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kgerbChief EngineerCommented:
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
 
dlmilleCommented:
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
 
Bright01Author 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.
0
 
kgerbChief EngineerCommented:
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
 
Bright01Author 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.
0
 
kgerbChief EngineerCommented:
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
 
Bright01Author 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.
0
 
kgerbChief EngineerCommented:
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
 
dlmilleCommented:
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
 
kgerbChief EngineerCommented:
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
 
Bright01Author 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.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now