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 Workbooks(Filename).Close Filename = Dir() Loop Application.ScreenUpdating = True End Sub
The highlighted part is where the error occurs. Please share your suggestions.