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

 

[ZVI]

Ok that works but when i run the code and get to the saveas dialogue box i press cancel the new wbk is still open, is it possible for the code to remove this workbook completely as the user had cancelled and only leave the master workbook open. If the user saves then its ok for the master workbook to save and close.

Isabella, you know the answer - it’s possible
See the code below:

Sub CopyShtsAsValues2()
  
  Dim Sh As Worksheet, sel As Range
  
  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
    Sh.Activate
    Set sel = Selection
    With Sh.UsedRange
      .Copy
      .PasteSpecial xlPasteValues
    End With
    sel.Select
  Next
  
  ' Make some sheets very hidden
  Sheets("RawData").Visible = xlSheetVeryHidden
  Sheets("FX").Visible = xlSheetVeryHidden
  
  ' Disable copy mode
  Application.CutCopyMode = False
  
  ' Call SaveAs dialog
  ChDrive [rngPath]   ' was: ThisWorkbook.Path
  ChDir [rngPath]     ' was: ThisWorkbook.Path
  CommandBars.FindControl(, 748).Execute
  
exit_:
  
  ' Restore screen on
  Application.ScreenUpdating = True
  
  ' Trap error
  If Err Then MsgBox Err.Description, vbCritical, "Error": Exit Sub
    
  ' Save & close master workbook
  If ActiveWorkbook.Saved Then
    ThisWorkbook.Save
    ThisWorkbook.Close
  Else
    ActiveWorkbook.Close False
    MsgBox "User Cancelled", vbExclamation, "Not saved"
  End If
    
End Sub

Isabella, I’m certain you are trying VBA to solve all issues you are asking for.
Even if it’s not successful it’s much desirable to show your attempts to avoid impression that instead of help in coding or in example as the direction you need in full support according to your serial needs.
I sincerely wish you success in learning and implementing VBA!

Kind Regards

 

[Isabella]

Thanks Vladimir. I am learning from the best, so your help has definately helped me with my task and from this i can take your logic of coding to future tasks. I know there are very good VBA people on this board who i have got help with, but in my opinion your coding style for me stands out and prior to you it was Seiya aka Jindon
I appreciate all your help on this task

Isabella, you know the answer - it’s possible
See the code below:

Sub CopyShtsAsValues2()
 
 Dim Sh As Worksheet, sel As Range
 
 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
   Sh.Activate
   Set sel = Selection
   With Sh.UsedRange
     .Copy
     .PasteSpecial xlPasteValues
   End With
   sel.Select
 Next
 
 ' Make some sheets very hidden
 Sheets("RawData").Visible = xlSheetVeryHidden
 Sheets("FX").Visible = xlSheetVeryHidden
 
 ' Disable copy mode
 Application.CutCopyMode = False
 
 ' Call SaveAs dialog
 ChDrive [rngPath]   ' was: ThisWorkbook.Path
 ChDir [rngPath]     ' was: ThisWorkbook.Path
 CommandBars.FindControl(, 748).Execute
 
exit_:
 
 ' Restore screen on
 Application.ScreenUpdating = True
 
 ' Trap error
 If Err Then MsgBox Err.Description, vbCritical, "Error": Exit Sub
 
 ' Save & close master workbook
 If ActiveWorkbook.Saved Then
   ThisWorkbook.Save
   ThisWorkbook.Close
 Else
   ActiveWorkbook.Close False
   MsgBox "User Cancelled", vbExclamation, "Not saved"
 End If
 
End Sub

Isabella, I’m certain you are trying VBA to solve all issues you are asking for.
Even if it’s not successful it’s much desirable to show your attempts to avoid impression that instead of help in coding or in example as the direction you need in full support according to your serial needs.
I sincerely wish you success in learning and implementing VBA!

Kind Regards

 

[Isabella]

Vladimir when i save the workbook i get the msg "User Canceled" i should only get this msg if i press cancel via the saveas dialogue box

Isabella, you know the answer - it’s possible
See the code below:

Sub CopyShtsAsValues2()
 
 Dim Sh As Worksheet, sel As Range
 
 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
   Sh.Activate
   Set sel = Selection
   With Sh.UsedRange
     .Copy
     .PasteSpecial xlPasteValues
   End With
   sel.Select
 Next
 
 ' Make some sheets very hidden
 Sheets("RawData").Visible = xlSheetVeryHidden
 Sheets("FX").Visible = xlSheetVeryHidden
 
 ' Disable copy mode
 Application.CutCopyMode = False
 
 ' Call SaveAs dialog
 ChDrive [rngPath]   ' was: ThisWorkbook.Path
 ChDir [rngPath]     ' was: ThisWorkbook.Path
 CommandBars.FindControl(, 748).Execute
 
exit_:
 
 ' Restore screen on
 Application.ScreenUpdating = True
 
 ' Trap error
 If Err Then MsgBox Err.Description, vbCritical, "Error": Exit Sub
 
 ' Save & close master workbook
 If ActiveWorkbook.Saved Then
   ThisWorkbook.Save
   ThisWorkbook.Close
 Else
   ActiveWorkbook.Close False
   MsgBox "User Cancelled", vbExclamation, "Not saved"
 End If
 
End Sub

Isabella, I’m certain you are trying VBA to solve all issues you are asking for.
Even if it’s not successful it’s much desirable to show your attempts to avoid impression that instead of help in coding or in example as the direction you need in full support according to your serial needs.
I sincerely wish you success in learning and implementing VBA!

Kind Regards

 

[Isabella]

When i save the new WKbk via Saveas dialogue box, the macro still returns msg "User Cancelled", i have been stepping through this code the code skips this part and jumps straight to Else

ThisWorkbook.Save
ThisWorkbook.Close

' Save & close master workbook
If ActiveWorkbook.Saved Then
ThisWorkbook.Save
ThisWorkbook.Close
Else
ActiveWorkbook.Close False
MsgBox "User Cancelled", vbExclamation, "Not saved"
Exit Sub
End If

Isabella, you know the answer - it’s possible
See the code below:

Sub CopyShtsAsValues2()
 
 Dim Sh As Worksheet, sel As Range
 
 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
   Sh.Activate
   Set sel = Selection
   With Sh.UsedRange
     .Copy
     .PasteSpecial xlPasteValues
   End With
   sel.Select
 Next
 
 ' Make some sheets very hidden
 Sheets("RawData").Visible = xlSheetVeryHidden
 Sheets("FX").Visible = xlSheetVeryHidden
 
 ' Disable copy mode
 Application.CutCopyMode = False
 
 ' Call SaveAs dialog
 ChDrive [rngPath]   ' was: ThisWorkbook.Path
 ChDir [rngPath]     ' was: ThisWorkbook.Path
 CommandBars.FindControl(, 748).Execute
 
exit_:
 
 ' Restore screen on
 Application.ScreenUpdating = True
 
 ' Trap error
 If Err Then MsgBox Err.Description, vbCritical, "Error": Exit Sub
 
 ' Save & close master workbook
 If ActiveWorkbook.Saved Then
   ThisWorkbook.Save
   ThisWorkbook.Close
 Else
   ActiveWorkbook.Close False
   MsgBox "User Cancelled", vbExclamation, "Not saved"
 End If
 
End Sub

Isabella, I’m certain you are trying VBA to solve all issues you are asking for.
Even if it’s not successful it’s much desirable to show your attempts to avoid impression that instead of help in coding or in example as the direction you need in full support according to your serial needs.
I sincerely wish you success in learning and implementing VBA!

Kind Regards

 

[Isabella]

anyone?