Hi,
I need to delete few sheets from multiple workbooks in given folder.
The sheets name are same in all the workbooks.
Please help
Hi,
I need to delete few sheets from multiple workbooks in given folder.
The sheets name are same in all the workbooks.
Please help
Possibly...
Option Explicit
Sub Test_on_File_Copies()
'!!! WARNING !!!
'This code will delete worksheets.
'---------------------------------------
Dim sGivenFolderPath As String, sExt As String
Dim vFiles As Variant, vName As Variant
Dim wb As Workbook, ws As Worksheet
sGivenFolderPath = "C:\Temp" 'change path and extension as needed
sExt = "xlsx"
vFiles = GetFiles(sGivenFolderPath, sExt)
If UBound(vFiles) = -1 Then
MsgBox " No files found."
Exit Sub
End If
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
For Each vName In vFiles
Set wb = Workbooks.Open(sGivenFolderPath & "\" & vName)
For Each ws In wb.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2" 'Worksheets to be deleted
ws.Delete
End Select
Next ws
wb.Close SaveChanges:=True
Next vName
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
Function GetFiles(sPath As String, Optional Ext As String) As Variant
Dim sFileName As String
With CreateObject("Scripting.Dictionary")
If Len(Ext) = 0 Then
Ext = "\*.*"
Else
Ext = "\*." & Ext
End If
sFileName = Dir(sPath & Ext, vbNormal)
Do While Not sFileName = vbNullString
.Item(sFileName) = Empty
sFileName = Dir
Loop
GetFiles = .Keys
End With
End Function
Display More
Great its working fine.
Thanks for your great Help
You're welcome!
Don’t have an account yet? Register yourself now and be a part of our community!