I have been trying to make a code that would run on all sheets a folder contains. But i am stuck that how to do this. I have wrote some line and gather information via google but difficult to create this one.
Here is the code which is required to run on all workbooks with sheet name i.e. DATA
Code
Here is the second one which provide the option to select the folder.
Code
- Sub Folder()
- Dim strFolder As String
- Dim strFile As String
- Dim wbk As Workbook
- Dim wsh As Worksheet
- Dim I As Long
- Dim xRg As Range
- With Application.FileDialog(4)
- If .Show Then
- strFolder = .SelectedItems(1)
- Else
- MsgBox "You haven't selected a folder!", vbExclamation
- Exit Sub
- End If
- End With
- Application.ScreenUpdating = False
- If Right(strFolder, 1) <> "\" Then
- strFolder = strFolder & "\"
- End If
- End Sub
- strFile = Dir(strFolder & "*.xls*")
- Do While strFile <> ""
- Set wbk = Workbooks.Open(strFolder & strFile)
- For I = 2 To wbk.Worksheets.Count
Your help will be highly appreciated.