Microsoft Office Forums - View Single Post - Copy Sheets to New Workbook BUT Keeping Format

@MacroZinck · 02-01-2018, 08:04 AM

Hi,

Would need help please with the simple macro below. It's a macro that copies a selection of worksheets to a new workbook and save it in a name indicated in the macro (e.g. 'France').

I'd like to add code lines for two purposes:
1. Keep the formatting of the original sheets in the newly created workbook
2. Set the view zoom to 55% for all sheets.

Would anyone help, please?

Thanks,

Code:

Sub CreateGroupFiles()
    
    Dim TheActiveWindow As Window
    'Dim TempWindow As Window
         
    'France
    With ActiveWorkbook
        Set TheActiveWindow = ActiveWindow
        .Sheets(Array("France", "_frly_o", "_frpac_o", "_frpad_o", "_frpacw_o)).Copy
            ActiveWorkbook.SaveAs Filename:="\\Sheets" & "\France.xlsx"
            ActiveWorkbook.Close
     End With
          
    'Germany
    With ActiveWorkbook
        Set TheActiveWindow = ActiveWindow
        .Sheets(Array("Germany", "_debe_o", "_dedu_o", "_defr_o", "_deha_o")).Copy
            ActiveWorkbook.SaveAs Filename:="\\Sheets" & "\Germany.xlsx"
            ActiveWorkbook.Close
     End With
          

End Sub