clear the data  of  multiples sheet

Sachin Singh
Sachin Singh used Ask the Experts™
on
My both files are located in same place i.e.  C:\Users\user\Desktop
My First file name is OneClick.xlsb
my second file name is ReFresh.xlsb
i will place the code in second file
now what i want is to clear all the data in the sheets of OneClick file (sheet1,sheet2,sheet3,sheet4,sheet6,sheet7) except sheet5
we don't have to delete the data in sheet5 leave this sheet
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...

Sub DeleteDataFromOneClickFile()
Dim wbOneclick As Workbook, wbRefresh As Workbook
Dim OneClickPath As String, OneClickName As String
Dim shArr, sh

Application.ScreenUpdating = False
OneClickPath = ThisWorkbook.Path & "\"
OneClickName = "OneClick.xlsb"
shArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet6", "Sheet7")
On Error Resume Next
Set wbOneclick = Workbooks.Open(OneClickPath & OneClickName)
For Each sh In shArr
    wbOneclick.Sheets(sh).Cells.Clear
Next sh
wbOneclick.Close True
Application.ScreenUpdating = True
MsgBox "Data from OneClick file has been cleared.", vbInformation
End Sub

Open in new window

Author

Commented:
Thnx Neeraj Sir for ur Great Support
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial