VBA Edit to Copy all but Sheet2 AND...

[Alex O]

I have my code mostly complete - except for a few minor edits that have me stumped! I need all sheets copied to the new workbook except Sheet2 (ODBC_TABLE). If possible, I'd also like no headings and password protected. Any help would be appreciated. Thanks

Sub New_Syscofile()
    Dim Output As Workbook
    Dim Current As String
    Dim FileName As String
    Set Output = ThisWorkbook
    Current = ThisWorkbook.FullName
    Application.DisplayAlerts = False
    Dim SH As Worksheet
    For Each SH In Output.Worksheets
        SH.UsedRange.Copy
        SH.UsedRange.PasteSpecial xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Next
    FileName = ThisWorkbook.Path & "\" & "SYSCO MIDWEST INV" & Format(Date, "mmddyyyy")
    Output.SaveAs FileName, XlFileFormat.xlOpenXMLWorkbook
    Workbooks.Open Current
    Application.DisplayAlerts = True
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.Zoom = 90
End Sub

 

[dnorm]

Hi Alex0

Have you tried recording a macro performing each task –
Start recording:
selecting each required sheet, copy into a new workbook.
Stop recording.
In the new work book Start recording:
Add password to required sheets/book.
Stop recording.

Then look at the macros you have recorded and see what processesyou can use i.e. the line for selecting each required sheet, passwordprotecting a sheet/book. Then adding those to your final macro.
Sorry I cannot be of more help.