I'm an Absolute nutshell in vba, looking for a macro that can combine all the workbooks present in the folder into a new workbook named "Final Data".
Below is the code
- Sub ConslidateWorkbooks()
- Dim FolderPath As String
- Dim Filename As String
- Dim Sheet As Worksheet
- Application.ScreenUpdating = False
- ''' highlighted part '''
- FolderPath = Environ("userprofile") & "\Desktop\Test\"
- Filename = Dir(FolderPath & "*.xls*")
- Do While Filename <> ""
- Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
- For Each Sheet In ActiveWorkbook.Sheets
- Sheet.Copy After:=ThisWorkbook.Sheets(1)
- Next Sheet
- Filename = Dir()
- Application.ScreenUpdating = True
- End Sub
The highlighted part is where the error occurs. Please share your suggestions.