How can i delete multiple sheets from multiple excel files, with the name Sheet+number

0
votes

I have a macro where, I want to apply data validation on every excel files in the folder save it and close it. But now I found out that this macro will apply on the first open sheet and not the sheet with file that is Name.LastName

Multiple of these files have sheets like

  • Sheet1
  • Sheet2
  • Sheet3
  • …
  • Name.LastName

How can I remove the Sheet1,2,3 or how many sheets there are. And only to stay Name.LastName

Sub LoopThroughFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
                 Columns("A:A").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveWorkbook.Save
    ActiveWorkbook.Close
            End With
            xFileName = Dir
        Loop
    End If
End Sub

Would be better if that code could be implemented here somehow

1
excelvba

1 Answers

0
votes

You will have to do a Worksheet looping on the active workbook. Try the below.

Sub LoopThroughFiles()
Dim xFd As FileDialog, xFdItem As Variant, xFileName As String, CrntWbk As Workbook, Ws As Worksheet
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)

If xFd.Show = -1 Then
    xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
    xFileName = Dir(xFdItem & "*.xls*")
    Do While xFileName <> ""
        Set CrntWbk = Workbooks.Open(xFdItem & xFileName)
        With CrntWbk
            For Each Ws In CrntWbk.Worksheets
                If Ws.Name = "Name.LastName" Then
                    ' If Worksheet Name is "Name.LastName", it applies the validation
                    With WS.Columns("A:A").Validation
                        .Delete
                        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                        :=xlBetween
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .ShowInput = True
                        .ShowError = True
                    End With
                Else
                    'Deletes other sheets
                    Application.DisplayAlerts = False
                    Ws.Delete
                    Application.DisplayAlerts = True
                End If
            Next Ws
            .Save
            .Close
        End With
        xFileName = Dir
    Loop
End If