Create a new excel doc from 5 sheets in another doc

[tonywatsonhelp]

Hi Everyone,

So I need a macro that can pull out (copy) 5 sheets from my excel document and create a new XLSM document

The sheets I want the use are

Data
Start
Report1
Report2
Report3

So I'd like to create this and save it to the same location as the current document, named whatever is in sheet"setup" cell G10

If possible I'd also like to make all formulas hidden
and sheet "Data" very hidden

please help if you can

Thanks

Tony

 

[kevin9999]

Hi Tony,

Please note that you can only hide formulas when you protect the sheet as well. The following code should give you what you want.

Option Explicit
Sub TonyWatsonHelp()    
    Dim FileName As String, wb As Workbook, sh As Worksheet
    FileName = ThisWorkbook.Sheets("setup").Range("G10").Value
    With ThisWorkbook.Sheets(Array("Data", "start", "Report1", "Report2", "Report3"))
        .Copy
    End With
    Set wb = ActiveWorkbook
    For Each sh In wb.Worksheets
        sh.UsedRange.SpecialCells(xlCellTypeFormulas).FormulaHidden = True
        sh.Protect Password:="123"  '<~~ Set your own password
    Next sh
    wb.Sheets("Data").Visible = xlVeryHidden
    wb.SaveAs ThisWorkbook.Path & "\" & FileName & ".xlsm", FileFormat:=52    
End Sub

 

[kevin9999]

Hi Everyone,

So I need a macro that can pull out (copy) 5 sheets from my excel document and create a new XLSM document

The sheets I want the use are

Data
Start
Report1
Report2
Report3

So I'd like to create this and save it to the same location as the current document, named whatever is in sheet"setup" cell G10

If possible I'd also like to make all formulas hidden
and sheet "Data" very hidden

please help if you can

Thanks

Tony

Did you try the suggested code?