Create new workbook from selected worksheets of master file (values only)

[berlinhammer]

Hello,

I have a very large workbook containing a lot of worksheets with a lot of data, formulae, data connections and macros. I like having the master file as a central source but I need to send it to various Global locations for verification, and the size and complexity of the file is a problem.

On a high level I think what would really be beneficial would be a macro which exports a group of selected worksheets to a new workbook, as values only and with any links/formulae/macros broken.

My googling leads to believe that this is more compliacted to achieve than I would have liked. Is this fair to say? My VBA is decent though far from expert, and I have never written anything involving grouped sheets or file creation before.

Has anyone come across a similar macro to the purpose I am describing or have a suggestion as to a good place to start? Such as the syntax for copying and exporting a group of sheets.

Grateful for any help or advice anyone can offer,

Thank you

Jon

 

[Isabella]

Vladimir, i noticed in your code the code copies to new workbook, i would prefer the code to not copy to new workbook but rather use this workbook as a template because once the user Saveas the template would not have been effected with the below actions.

Try this code:

Sub CopyShtsAsValues()
  
  Dim Sh As Worksheet
  
  On Error GoTo exit_
  
  ' Screen off
  Application.ScreenUpdating = False
  
  ' Copy sheets into new workbook
  Sheets.Copy
  
  ' Replace formulas by values
  For Each Sh In Worksheets
    Sh.Visible = xlSheetVisible
    With Sh.UsedRange
      .Copy
      .PasteSpecial xlPasteValues
    End With
  Next
  
  ' Make some sheets very hidden
  Sheets("RawData").Visible = xlSheetVeryHidden
  Sheets("FX").Visible = xlSheetVeryHidden
  
  ' Disable copy mode, restore screen on
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
  ' Call SaveAs dialog
  ChDrive ThisWorkbook.Path
  ChDir ThisWorkbook.Path
  CommandBars.FindControl(, 748).Execute
  
exit_:
  
  ' Trap error
  If Err Then MsgBox Err.Description, vbCritical, "Error"
  
End Sub

 

[ZVI]

Isabella, just comment this line: Sheets.Copy
Please take into account that the thread is named as Create NEW workbook from SELECTED worksheets, but your task is now different to the original.
Regards

 

[Isabella]

Isabella, just comment this line: Sheets.Copy
Please take into account that the thread is named as Create NEW workbook from SELECTED worksheets, but your task is now different to the original.
Regards

Thanks Vladimir. I did not create this thread, i thought it was similar to my task so i continued with the thread.

 

[ZVI]

Isabella, it’s ok, but it was the reason for me to think about new workbook.
BTW replacing of formulas by values in active workbook is not safety in case of user choose Cancel and will save workbook later with the same name (without formulas).
But there is no issue if you really use XLT template file not just XLS workbook as template.

 

[Isabella]

Isabella, it’s ok, but it was the reason for me to think about new workbook.
BTW replacing of formulas by values in active workbook is not safety in case of user choose Cancel and will save workbook later with the same name (without formulas).
But there is no issue if you really use XLT template file not just XLS workbook as template.

Vladimir, i was thinking the same thing, the file is XLS workbook. What do you propose as an alternative if the user cancel then Exit Sub?

 

[ZVI]

Vladimir, i was thinking the same thing, the file is XLS workbook. What do you propose as an alternative if the user cancel then Exit Sub?

For me the using of new workbook is more safe

 

[Isabella]

For me the using of new workbook is more safe

Vladimir, i have taken your suggestion of copying to a new wkbk by uncommenting the Sheets.Copy, when i save the wkbk and re-open all the worksheets are selected, i had to go to each worksheet and select a cell, can this be adopted in code?

 

[ZVI]

Vladimir, i have taken your suggestion of copying to a new wkbk by uncommenting the Sheets.Copy, when i save the wkbk and re-open all the worksheets are selected, i had to go to each worksheet and select a cell, can this be adopted in code?

Isabella,

There is nothing in the code to select all sheets.
Copying of already selected sheets was used only in post #2 for Jon’s task.

If sheets been selected before running of code, then for deselecting it, add the following code below the line Sheets.Copy:
Sheets(1).Select

Regards

 

[Isabella]

Isabella,

There is nothing in the code to select all sheets.
Copying of already selected sheets was used only in post #2 for Jon’s task.

If sheets been selected before running of code, then for deselecting it, add the following code below the line Sheets.Copy:
Sheets(1).Select

Regards

Vladimir, is it possible to save and close the master workbook via code to avoid any confusion with the the new wkbk?

 

[ZVI]

Vladimir, is it possible to save and close the master workbook via code to avoid any confusion with the the new wkbk?

Sure, Isabella.

1. Saving: ThisWorkbook.Save

2. Closing: ThisWorkbook.Close
If there are not saved changes in closing workbook, then Excel prompts for saving changes.

Regards