none
Excluding specified sheets RRS feed

  • Question

  • Good Day

    I'm interested in running a macro that I have, and I want to exclude two specific sheets-- they are named. The workbook is some 7 or 8 worksheets. The remaining sheet names-- that I do want operated on-- are varied, and diverse, so attempting to set a case statement for them will not work. As I don't have much experience with this, I have only managed to write code to exclude one sheet (called NAV).

    For Each sht In wbkSRC.Sheets

    Set shtSRC = sht
            If sht.Name <> "NAV" Then
            End If

    When I try to exclude another sheet (using the following code), it fails

    For Each sht In wbkSRC.Sheets

    Set shtSRC = sht
            If sht.Name <> "NAV" And sht.Name <> "TB" Then
            End If

    Please could someone assist me?

    Thursday, August 30, 2018 8:06 AM

All replies

  • what do you mean with fails?

    your syntax looks correct  ( I assume you forget to copy "Next" which end the for statement)

    Thursday, August 30, 2018 9:35 AM
  • Hi TNDH,

    You can exclude specified sheets and delete them by using the code below:

    Sub SheetKiller()
        Dim s As Worksheet, t As String
        Dim i As Long, K As Long
        K = Sheets.Count
    
        For i = K To 1 Step -1
            t = Sheets(i).Name
            If t = "NAV" Or t = "TB" Then
                Application.DisplayAlerts = False
                    Sheets(i).Delete
                Application.DisplayAlerts = True
            End If
        Next i
    End Sub
    

    Hopefully it helps you. Please feel free to ask any questions. Looking forward to your reply.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, August 30, 2018 9:45 AM
    Moderator
  • Not sure how to alter the existing syntax, let's go back a step. The full sub is below (you'll see my 'if' statement in the middle):

    Sub Import_fromFile()
    
    'Declarations
    Dim wbkSRC As Workbook
    Dim shtSRC As Worksheet, shtDEST As Worksheet, sht As Worksheet, shtGenDEST As Worksheet, shtGenSRC As Worksheet
    Dim lrow As Double, lrow_Report As Double, lcol As Double, icol As Double, irow As Double, irow2 As Double, dHeader As Double, sClass As String
    Dim dY As Double, dM As Double
    
    'Referencing
    Set shtDEST = ThisWorkbook.Sheets("Report")
    lrow_Report = LastRow(shtDEST)
    Set wbkSRC = Workbooks.Open(Application.GetOpenFilename)
    
    'First import general data
    Set shtGenDEST = ThisWorkbook.Sheets("Basic details")
    Set shtGenSRC = wbkSRC.Sheets("Basic details")
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI CSSF Code"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "XXXX Code"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Name"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "XXXX Name"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Base CCY"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "XXXX"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Launch Date"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "XXXX Date"), 2)
    
    
    'Input everything that is not xxxx
    For Each sht In wbkSRC.Sheets
    
    Set shtSRC = sht
            Select Case sht.Name
            Case "NAV"
            Case Else
            End Select
            
    Debug.Print sht.Name
    
    'last row always determined by date entered on basic details
    lrow = shtSRC.Cells(65000, 4).End(xlUp).Row
    
    For irow = lrow To 1 Step -1
    dY = Year(shtSRC.Cells(irow, 2))
    dM = Month(shtSRC.Cells(irow, 2))
    
    If dY = Year(ThisWorkbook.Sheets("Basic details").Range("B13")) And dM = Month(ThisWorkbook.Sheets("Basic details").Range("B13")) Then
    lrow = irow
    Exit For
    End If
    Next irow
    
    lcol = LastCol(shtSRC)
    
    
    For irow = 3 To lrow_Report
    
        For icol = 1 To lcol
        If IsNumeric(shtSRC.Cells(2, icol)) And shtSRC.Cells(2, icol) > 0 Then
        dHeader = shtSRC.Cells(2, icol)
        shtDEST.Cells(irow, FindCol_num(1, shtDEST, dHeader)) = shtSRC.Cells(lrow, FindCol_num(2, shtSRC, dHeader))
        End If
        Next icol
    
    Next irow
    
    Next sht
    
    'Input share class info
    For irow = 3 To lrow_Report
    
    'Get XXXX
    sClass = shtDEST.Cells(irow, FindCol_string(2, shtDEST, "XXXX Name"))
    
    'Get source XXXX sheet
    Set shtSRC = wbkSRC.Sheets(sClass)
    
    'last row always determined by date entered on basic details
    lrow = shtSRC.Cells(65000, 4).End(xlUp).Row
    
    For irow2 = lrow To 1 Step -1
    dY = Year(shtSRC.Cells(irow2, 2))
    dM = Month(shtSRC.Cells(irow2, 2))
    
    If dY = Year(ThisWorkbook.Sheets("Basic details").Range("B13")) And dM = Month(ThisWorkbook.Sheets("Basic details").Range("B13")) Then
    lrow = irow2
    Exit For
    End If
    Next irow2
    
    
    lcol = LastCol(shtSRC)
    
        For icol = 1 To lcol
        If IsNumeric(shtSRC.Cells(2, icol)) And shtSRC.Cells(2, icol) > 0 Then
        dHeader = shtSRC.Cells(2, icol)
        shtDEST.Cells(irow, FindCol_num(1, shtDEST, dHeader)) = shtSRC.Cells(lrow, FindCol_num(2, shtSRC, dHeader))
        End If
        Next icol
    
    Next irow
    
    
    wbkSRC.Close SaveChanges:=False
    
    End Sub


    When I add another sheet to exclude the macro stops working.


    Thursday, August 30, 2018 2:00 PM
  • Thanks Yuki, but I don't want to delete the unused sheets - I just want them to be ignored.

    Thursday, August 30, 2018 2:07 PM
  • Hi TNDH,

    Sorry for the late reply. To exclude the specific sheet in excel and select the others, you can try the VBA code below:

    Sub selectallbutone() Dim x As Long

    Sheet1.Select For x = 2 To ThisWorkbook.Sheets.Count If Sheets(x).Name <> "NAV" Then Sheets(x).Select Replace:=False Next x End Sub

    For more information, please review the following link: Select All Sheet Tabs Except Specific One With VBA Code

    Hopefully it helps you. Please feel free to ask any questions. Looking forward to your reply.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    Monday, September 3, 2018 5:59 AM
    Moderator
  • Hi Yuki

    The code excludes the sheet called "NAV" which is great, but I need to exclude multiple sheets. How do I alter the code?

    Already tried a few things but no luck?

    Thanks

    Monday, September 3, 2018 7:32 AM
  • Hi TNDH,

    To exclude multiple sheets, we can use code below to replace the fourth line of code above:

    If Sheets(x).Name <> "NAV" And Sheets(x).Name <> "TB" Then Sheets(x).Select Replace:=False

    Looking forward to hearing from you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 3, 2018 7:52 AM
    Moderator
  • It gives Run-time error ‘13’:

    Monday, September 3, 2018 8:22 AM
  • Hi,

    It works well in my Excel 2016. Could you please post your complete code here? 

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Monday, September 3, 2018 8:45 AM
    Moderator
  • Sub Import_fromFile()

    'Declarations
    Dim wbkSRC As Workbook
    Dim shtSRC As Worksheet, shtDEST As Worksheet, sht As Worksheet, shtGenDEST As Worksheet, shtGenSRC As Worksheet
    Dim lrow As Double, lrow_Report As Double, lcol As Double, icol As Double, irow As Double, irow2 As Double, dHeader As Double, sClass As String
    Dim dY As Double, dM As Double

    'Referencing
    Set shtDEST = ThisWorkbook.Sheets("Report")
    lrow_Report = LastRow(shtDEST)
    Set wbkSRC = Workbooks.Open(Application.GetOpenFilename)

    'First import general data
    Set shtGenDEST = ThisWorkbook.Sheets("Basic details")
    Set shtGenSRC = wbkSRC.Sheets("Basic details")
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI CSSF Code"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "UCI CSSF Code"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Name"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "UCI Name"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Base CCY"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "UCI Base CCY"), 2)
    shtGenDEST.Cells(FindRow_string(1, shtGenDEST, "UCI Launch Date"), 2) = shtGenSRC.Cells(FindRow_string(1, shtGenSRC, "UCI Launch Date"), 2)


    'Input everything that is not share class
    For Each sht In wbkSRC.Sheets

    Set shtSRC = sht
           
    Debug.Print sht.Name

    Dim x As Long

    For x = 2 To ThisWorkbook.Sheets.Count
        If Sheets(x).Name <> "NAV" And Sheets(x).Name <> "TB" Then Sheets(x).Select Replace:=False
           
        Next x


    'last row always determined by date entered on basic details
    lrow = shtSRC.Cells(65000, 4).End(xlUp).Row

    For irow = lrow To 1 Step -1
    dY = Year(shtSRC.Cells(irow, 2))
    dM = Month(shtSRC.Cells(irow, 2))

    If dY = Year(ThisWorkbook.Sheets("Basic details").Range("B13")) And dM = Month(ThisWorkbook.Sheets("Basic details").Range("B13")) Then
    lrow = irow
    Exit For
    End If
    Next irow

    lcol = LastCol(shtSRC)


    For irow = 3 To lrow_Report

        For icol = 1 To lcol
        If IsNumeric(shtSRC.Cells(2, icol)) And shtSRC.Cells(2, icol) > 0 Then
        dHeader = shtSRC.Cells(2, icol)
        shtDEST.Cells(irow, FindCol_num(1, shtDEST, dHeader)) = shtSRC.Cells(lrow, FindCol_num(2, shtSRC, dHeader))
        End If
        Next icol

    Next irow

    Next sht

    'Input share class info
    For irow = 3 To lrow_Report

    'Get class
    sClass = shtDEST.Cells(irow, FindCol_string(2, shtDEST, "Share Class Name"))

    'Get source class sheet
    Set shtSRC = wbkSRC.Sheets(sClass)

    'last row always determined by date entered on basic details
    lrow = shtSRC.Cells(65000, 4).End(xlUp).Row

    For irow2 = lrow To 1 Step -1
    dY = Year(shtSRC.Cells(irow2, 2))
    dM = Month(shtSRC.Cells(irow2, 2))

    If dY = Year(ThisWorkbook.Sheets("Basic details").Range("B13")) And dM = Month(ThisWorkbook.Sheets("Basic details").Range("B13")) Then
    lrow = irow2
    Exit For
    End If
    Next irow2


    lcol = LastCol(shtSRC)

        For icol = 1 To lcol
        If IsNumeric(shtSRC.Cells(2, icol)) And shtSRC.Cells(2, icol) > 0 Then
        dHeader = shtSRC.Cells(2, icol)
        shtDEST.Cells(irow, FindCol_num(1, shtDEST, dHeader)) = shtSRC.Cells(lrow, FindCol_num(2, shtSRC, dHeader))
        End If
        Next icol

    Next irow


    wbkSRC.Close SaveChanges:=False

    End Sub

    Monday, September 3, 2018 8:47 AM
  • The macro workbook is MS Office 2013 Professional Plus


    • Edited by TNDH Monday, September 3, 2018 8:58 AM
    Monday, September 3, 2018 8:56 AM
  • Hi TNDH,

    There must be something else missing such as function "LastRow","FindRow_string" and ect.

    I can't run your code since I get an error "Sub or Function not defined”. 

    This is the simplified code as I can run it successfully. Code shown as below:

    Option Explicit
    Sub Import_fromFile()
    'Declarations
    Dim shtSRC As Worksheet, shtDEST As Worksheet, sht As Worksheet, shtGenDEST As Worksheet, shtGenSRC As Worksheet
    'Input everything that is not share class
    For Each sht In ThisWorkbook.Sheets
    Debug.Print sht.Name
    Dim x As Long
    For x = 1 To ThisWorkbook.Sheets.Count
        If Sheets(x).Name <> "NAV" And Sheets(x).Name <> "TB" Then Sheets(x).Select Replace:=False        
        Next x
    Next sht
    End Sub

    It works well for me. Could you try run it to see if it works for you without the other “Import_fromFile” part? 

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Monday, September 3, 2018 9:45 AM
    Moderator
  • Hi Yuki

    Not sure I understand (I have very little understanding of writing code, the whole syntax was written by someone else at my company who has since left! So please spoonfeed me on this) Anyhow, I pasted your simplified code over all the code in my macro....there was no error but nothing happened. Right?

    Monday, September 3, 2018 10:37 AM
  • Hi TNDH,

    The code above is about ignore specified sheets such as "NAV","TB" and select the others in your workbook. It is not nothing happened. To prove that, I record a gif for you. 

    The code is not complete so i can't know what's wrong with it. If possible, you could post your excel here and i will run your macro to troubleshoot problem.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 3, 2018 11:17 AM
    Moderator
  • Hi TNDH,

    Thanks for your quick response. Please remember to mark the replies(Include your solution) as answers if they helped and please help us close the thread.

    Thank you for understanding. If you have any question, or update, please feel free to let us know.
    I wish you a happy life!

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 10, 2018 2:22 AM
    Moderator