Results 1 to 2 of 2

Thread: VBA Edit to Copy all but Sheet2 AND...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Alex O's Avatar
    Join Date
    Mar 2009
    Location
    New Orleans, LA
    Posts
    335
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Edit to Copy all but Sheet2 AND...

    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

    Code:
    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

  2. #2
    New Member
    Join Date
    Dec 2017
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Edit to Copy all but Sheet2 AND...

    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.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •